SQLite如何查询两表变更值并将未变化的匹配值置为NULL

实现逻辑 #

• 通过id字段关联New表和Old表
• 对每个非id字段做条件判断：如果New表的字段值和Old表不一致，保留New表的值，否则置为NULL
• 最后过滤掉所有字段都未变更的行

通用SQL语句（支持PostgreSQL、SQLite等支持IS DISTINCT FROM语法的数据库） #

SELECT
  n.id,
  CASE WHEN n.title IS DISTINCT FROM o.title THEN n.title ELSE NULL END AS title,
  CASE WHEN n.views IS DISTINCT FROM o.views THEN n.views ELSE NULL END AS views
FROM New n
INNER JOIN Old o ON n.id = o.id
WHERE n.title IS DISTINCT FROM o.title OR n.views IS DISTINCT FROM o.views;

MySQL兼容版SQL语句 #

MySQL不支持IS DISTINCT FROM语法，可改用不等判断加空值校验实现：

SELECT
  n.id,
  CASE WHEN n.title <> o.title OR (n.title IS NULL <> o.title IS NULL) THEN n.title ELSE NULL END AS title,
  CASE WHEN n.views <> o.views OR (n.views IS NULL <> o.views IS NULL) THEN n.views ELSE NULL END AS views
FROM New n
INNER JOIN Old o ON n.id = o.id
WHERE (n.title <> o.title OR (n.title IS NULL <> o.title IS NULL))
OR (n.views <> o.views OR (n.views IS NULL <> o.views IS NULL));

上述语句执行后输出结果和你给出的预期结果完全一致。

内容的提问来源于stack exchange，提问作者waivek