MS SQL中通过自外连接关联昨日与今日数据以实现数值差异及新增/缺失数据查询的技术问询

你好！你的思路方向是对的——用外连接确实能解决这个问题，之前的尝试没成功主要是因为没用到全外连接（FULL OUTER JOIN），而且日期条件的位置和连接逻辑没处理好。下面给你一个更简洁高效的方案，不需要临时表就能实现需求：

我们可以用**公共表表达式（CTE）**分别提取昨日和今日的数据集，逻辑清晰且不需要创建临时表，再通过全外连接关联这两个数据集，就能覆盖你需要的三种场景：

WITH YesterdayData AS (
    SELECT ID, task_id, number
    FROM your_table_name
    -- 替换成你的昨日日期，也可以用动态日期：DATEADD(day, -1, CAST(GETDATE() AS DATE))
    WHERE date_stored = '2021-01-18'
),
TodayData AS (
    SELECT ID, task_id, number
    FROM your_table_name
    -- 替换成你的今日日期，动态日期用：CAST(GETDATE() AS DATE)
    WHERE date_stored = '2021-01-19'
)
SELECT
    -- 用COALESCE确保ID和task_id不会显示空值
    COALESCE(y.ID, t.ID) AS ID,
    COALESCE(y.task_id, t.task_id) AS task_id,
    y.number AS yesterday_number,
    t.number AS today_number,
    -- 标记变化类型，方便快速识别
    CASE
        WHEN y.number IS NULL THEN '今日新增'
        WHEN t.number IS NULL THEN '今日消失'
        ELSE '数值变化'
    END AS change_type,
    -- 计算差值，仅当两边都有数据时生效
    CASE
        WHEN y.number IS NOT NULL AND t.number IS NOT NULL THEN t.number - y.number
        ELSE NULL
    END AS value_diff
FROM YesterdayData y
FULL OUTER JOIN TodayData t
    ON y.ID = t.ID AND y.task_id = t.task_id
-- 筛选出我们需要的三种情况
WHERE
    y.number IS NULL -- 今日新增的记录
    OR t.number IS NULL -- 昨日存在今日消失的记录
    OR y.number <> t.number -- 数值有增减的记录
ORDER BY ID, task_id;

方案说明 #

• CTE的优势：把昨日和今日的数据单独筛选出来，避免重复写日期条件，让代码更易读和维护。
• 全外连接的作用：和左/右连接不同，全外连接会返回两个数据集中的所有记录，不管是否匹配，这样就能同时捕获“昨日有今日无”和“今日有昨日无”的场景。
• COALESCE函数：处理连接时的空值问题，确保即使其中一方没有数据，ID和task_id也能正常显示。
• 筛选逻辑：直接过滤出三种目标记录，排除掉数值没有变化的冗余数据。

针对你之前尝试的问题分析 #

1. 第一个查询用了隐式内连接（逗号分隔表），这种写法只会返回两边都匹配的记录，自然漏掉了新增和消失的情况。
2. 第二个查询用了左连接，但只以昨日数据为左表，无法捕获今日新增的记录；同时把日期条件写在WHERE子句里，会过滤掉左连接中右表为空的部分，导致逻辑出错。

测试你的示例数据 #

用你给出的示例数据运行上面的查询，会得到以下结果（数值无变化的记录会被过滤）：

完全符合你的需求！

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