Postgres版本跟踪表跨版本查询缺失项的实现方案咨询

不需要额外安装插件，用Postgres原生语法即可实现，无需修改原表结构。

核心逻辑 #

1. 先提取表中所有不重复的版本号
2. 生成所有需要对比的版本组合（源版本、目标版本）
3. 分别关联源版本和目标版本的条目，过滤出源版本存在、目标版本不存在的条目

视图创建SQL #

CREATE VIEW compare_version_missing AS
WITH distinct_versions AS (
    -- 提取所有不重复的版本号
    SELECT DISTINCT v FROM compare_test
)
SELECT
    vs.v AS source_v,
    vt.v AS target_v,
    cs.i AS source_i,
    ct.i AS target_i
FROM distinct_versions vs
-- 生成所有版本组合
CROSS JOIN distinct_versions vt
-- 关联源版本对应的所有条目
LEFT JOIN compare_test cs ON cs.v = vs.v
-- 关联目标版本对应的同一条目
LEFT JOIN compare_test ct ON ct.v = vt.v AND ct.i = cs.i
WHERE
    -- 排除同一个版本对比自己的场景
    vs.v <> vt.v
    -- 仅对比目标版本号大于源版本的场景，和示例输出匹配，如不需要可删除该行
    AND vt.v > vs.v
    -- 筛选目标版本不存在的条目
    AND ct.i IS NULL;

结果验证 #

创建视图后执行SELECT * FROM compare_version_missing，即可得到你要求的预期输出：

如果需要对比指定的两个版本，直接加过滤条件即可，比如查询版本1和版本2的差异：

SELECT * FROM compare_version_missing WHERE source_v = 1 AND target_v = 2;

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