Oracle数据库中如何查询A表与A_Hist表各列的差异值？

当然可以！针对你的表结构（A表存当前数据，A_Hist存历史快照，仅多一个timestamp列），我们可以通过几种实用的SQL方法来找出两张表各列的差异，我会结合不同的业务场景给出具体实现：

场景1：对比A表当前数据与对应id的最新历史快照差异 #

如果你的需求是看每个id的当前值和它最近一次历史快照的差异，可以先用CTE获取每个id的最新历史时间戳，再关联两张表做列对比：

WITH Latest_Hist AS (
    SELECT id, MAX(timestamp) AS latest_ts
    FROM A_Hist
    GROUP BY id
)
SELECT 
    a.id,
    -- 对比a列差异（处理NULL值场景）
    CASE 
        WHEN (a.a <> ah.a) OR (a.a IS NULL AND ah.a IS NOT NULL) OR (a.a IS NOT NULL AND ah.a IS NULL)
        THEN CONCAT('当前值: ', COALESCE(CAST(a.a AS VARCHAR), 'NULL'), ' | 历史值: ', COALESCE(CAST(ah.a AS VARCHAR), 'NULL')) 
        ELSE '无差异' 
    END AS a_diff,
    -- 对比b列差异（处理NULL值场景）
    CASE 
        WHEN (a.b <> ah.b) OR (a.b IS NULL AND ah.b IS NOT NULL) OR (a.b IS NOT NULL AND ah.b IS NULL)
        THEN CONCAT('当前值: ', COALESCE(CAST(a.b AS VARCHAR), 'NULL'), ' | 历史值: ', COALESCE(CAST(ah.b AS VARCHAR), 'NULL')) 
        ELSE '无差异' 
    END AS b_diff,
    -- 以此类推扩展到c、d等其他列
    CASE 
        WHEN (a.c <> ah.c) OR (a.c IS NULL AND ah.c IS NOT NULL) OR (a.c IS NOT NULL AND ah.c IS NULL)
        THEN CONCAT('当前值: ', COALESCE(CAST(a.c AS VARCHAR), 'NULL'), ' | 历史值: ', COALESCE(CAST(ah.c AS VARCHAR), 'NULL')) 
        ELSE '无差异' 
    END AS c_diff,
    ah.timestamp AS historical_timestamp
FROM A a
JOIN Latest_Hist lh ON a.id = lh.id
JOIN A_Hist ah ON lh.id = ah.id AND lh.latest_ts = ah.timestamp;

这段代码会输出每个id的当前值与最新历史值的差异情况，同时处理了列值为NULL的特殊场景（因为NULL和任何值直接用<>比较都会返回UNKNOWN，需要单独判断）。

场景2：追踪同一id在A_Hist中的历史变更差异 #

如果想查看某个id的历史记录中，每一次时间戳对应的列值变化，可以用窗口函数LAG()来获取上一个时间点的列值，再做对比：

SELECT 
    id,
    timestamp,
    -- 对比a列与上一个时间点的差异（处理NULL值场景）
    CASE 
        WHEN (a <> LAG(a) OVER (PARTITION BY id ORDER BY timestamp)) 
            OR (a IS NULL AND LAG(a) OVER (PARTITION BY id ORDER BY timestamp) IS NOT NULL)
            OR (a IS NOT NULL AND LAG(a) OVER (PARTITION BY id ORDER BY timestamp) IS NULL)
        THEN CONCAT('变更前: ', COALESCE(CAST(LAG(a) OVER (PARTITION BY id ORDER BY timestamp) AS VARCHAR), 'NULL'), ' | 变更后: ', COALESCE(CAST(a AS VARCHAR), 'NULL')) 
        ELSE '无变更' 
    END AS a_diff,
    -- 同理处理b、c、d等列
    CASE 
        WHEN (b <> LAG(b) OVER (PARTITION BY id ORDER BY timestamp)) 
            OR (b IS NULL AND LAG(b) OVER (PARTITION BY id ORDER BY timestamp) IS NOT NULL)
            OR (b IS NOT NULL AND LAG(b) OVER (PARTITION BY id ORDER BY timestamp) IS NULL)
        THEN CONCAT('变更前: ', COALESCE(CAST(LAG(b) OVER (PARTITION BY id ORDER BY timestamp) AS VARCHAR), 'NULL'), ' | 变更后: ', COALESCE(CAST(b AS VARCHAR), 'NULL')) 
        ELSE '无变更' 
    END AS b_diff
FROM A_Hist
ORDER BY id, timestamp;

这段代码会按id分组、时间戳排序，展示每一条历史记录相对于上一次记录的列值变化，适合追踪数据的历史变更轨迹。

场景3：对比A表与A_Hist中所有同id记录的差异 #

如果需要一次性查看A表和A_Hist中所有同id记录的差异（不管时间戳），可以用FULL JOIN来关联两张表，覆盖所有存在于任一表中的id：

SELECT 
    COALESCE(a.id, ah.id) AS id,
    COALESCE(CAST(a.a AS VARCHAR), 'A表无此记录') AS current_a,
    COALESCE(CAST(ah.a AS VARCHAR), 'A_Hist无此记录') AS historical_a,
    CASE 
        WHEN (a.a <> ah.a) OR (a.a IS NULL AND ah.a IS NOT NULL) OR (a.a IS NOT NULL AND ah.a IS NULL)
        THEN '存在差异' 
        ELSE '无差异' 
    END AS a_diff,
    -- 同理处理b、c、d等列
    COALESCE(CAST(a.b AS VARCHAR), 'A表无此记录') AS current_b,
    COALESCE(CAST(ah.b AS VARCHAR), 'A_Hist无此记录') AS historical_b,
    CASE 
        WHEN (a.b <> ah.b) OR (a.b IS NULL AND ah.b IS NOT NULL) OR (a.b IS NOT NULL AND ah.b IS NULL)
        THEN '存在差异' 
        ELSE '无差异' 
    END AS b_diff,
    ah.timestamp AS historical_timestamp
FROM A a
FULL JOIN A_Hist ah ON a.id = ah.id;

这种方式会列出所有匹配和不匹配的id记录，清晰展示哪些id在某张表中不存在，以及存在的记录中列值是否有差异。

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