Informix 11.70：高效查询两相似表数据差异并生成报告

嘿，针对你在Informix 11.70里对比Live和Archive表数据差异的需求，我有几个比Union更高效的方案，而且能完美生成你要的包含Live数据及历史差异的报告。

核心思路：用关联查询替代Union #

Union查询需要对两个结果集做排序去重，数据量大的时候开销很高。而通过关联匹配+条件过滤，我们可以直接定位到差异记录，效率提升明显。

方案1：展示当前值+历史差异汇总（适合概览报告） #

这个查询会返回Live表的当前数据，以及对应的最新归档日期、最近一次的差异值、最早的历史值，帮你快速了解数据变化轨迹：

SELECT
    l.Name,
    l.ID,
    l.TRN AS Live_TRN,
    MAX(a.Date) AS Latest_Archive_Date,
    -- 找到最近一次与当前TRN不同的历史值
    MAX(CASE WHEN a.TRN != l.TRN THEN a.TRN END) AS Previous_TRN,
    -- 显示最早的TRN值，完整展示变化过程
    MIN(a.TRN) AS Oldest_TRN
FROM
    Live l
LEFT JOIN
    Archive a ON l.Name = a.Name AND l.ID = a.ID
GROUP BY
    l.Name, l.ID, l.TRN
-- 只过滤出有差异的记录（可选：去掉OR条件则只显示有差异的）
HAVING
    COUNT(CASE WHEN a.TRN != l.TRN THEN 1 END) > 0
    OR COUNT(a.TRN) = 0;

结果示例（匹配你的测试数据）：

方案2：展示所有历史差异明细（适合详细报告） #

如果你需要每条历史差异的具体记录，这个查询会列出所有与Live当前值不同的归档记录，同时补充Live表有但归档没有的条目：

-- 先列出所有与Live数据有差异的归档记录
SELECT
    l.Name,
    l.ID,
    l.TRN AS Live_TRN,
    a.Date AS Archive_Date,
    a.TRN AS Archive_TRN,
    'TRN Changed from ' || a.TRN || ' to ' || l.TRN AS Change_Description
FROM
    Live l
JOIN
    Archive a ON l.Name = a.Name AND l.ID = a.ID
WHERE
    a.TRN != l.TRN
-- 补充Live表存在但Archive表没有的记录
UNION ALL
SELECT
    Name,
    ID,
    TRN AS Live_TRN,
    NULL AS Archive_Date,
    NULL AS Archive_TRN,
    'No Archive records found' AS Change_Description
FROM
    Live l
WHERE
    NOT EXISTS (SELECT 1 FROM Archive a WHERE l.Name = a.Name AND l.ID = a.ID);

结果示例：

性能优化建议 #

为了让这些查询跑得更快，建议给两张表的关联字段创建索引：

CREATE INDEX idx_archive_name_id ON Archive(Name, ID);
CREATE INDEX idx_live_name_id ON Live(Name, ID);

Informix 11.70对带索引的关联查询优化很好，能大幅减少扫描的数据量。

为什么比Union高效？ #

普通Union会对两个完整结果集做排序和去重操作，当表数据量较大时，这会带来很高的IO和CPU开销。而上面的方案：

• 用JOIN直接匹配差异记录，只扫描需要的数据
• NOT EXISTS子查询会利用索引快速判断是否存在匹配记录，避免全表扫描
• UNION ALL不需要去重，比Union的开销小很多

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