SQL Server 2016无主键表两日业务数据新增与变更行查询需求

针对你在SQL Server 2016中对比无主键表两日滚动BUSINESS_DATE数据的需求，这里提供两种可行方案，解决你此前FULL OUTER JOIN未达预期的问题：

核心逻辑 #

由于表无主键，我们需要以除BUSINESS_DATE外的所有业务字段组合作为行的唯一标识，通过对比两日的这些组合，区分「新增（NEW）」和「变更（CHANGED）」行。

方案1：直接对比所有业务字段 #

先提取两日的数据子集，再通过FULL JOIN关联所有业务字段，判断行状态：

WITH yesterday_data AS (
    SELECT *
    FROM your_table
    WHERE BUSINESS_DATE = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
),
today_data AS (
    SELECT *
    FROM your_table
    WHERE BUSINESS_DATE = CAST(GETDATE() AS DATE)
)
SELECT 
    -- 用COALESCE确保字段值非空，优先取当日数据
    COALESCE(t.COL1, y.COL1) AS COL1,
    COALESCE(t.COL2, y.COL2) AS COL2,
    -- 其他业务字段同理添加
    CASE
        -- 新增：昨日无匹配行，今日存在
        WHEN y.BUSINESS_DATE IS NULL THEN 'NEW'
        -- 变更：两日都有匹配行，但字段值不全一致
        WHEN EXISTS (
            SELECT t.COL1, t.COL2, /* 所有业务字段 */
            EXCEPT
            SELECT y.COL1, y.COL2, /* 所有业务字段 */
        ) THEN 'CHANGED'
    END AS STATUS
FROM today_data t
FULL OUTER JOIN yesterday_data y
    -- 关联所有业务字段，模拟主键匹配
    ON t.COL1 = y.COL1
    AND t.COL2 = y.COL2
    /* 其他业务字段的关联条件 */
WHERE 
    -- 仅保留新增或变更的行
    y.BUSINESS_DATE IS NULL 
    OR EXISTS (
        SELECT t.COL1, t.COL2, /* 所有业务字段 */
        EXCEPT
        SELECT y.COL1, y.COL2, /* 所有业务字段 */
    )

方案2：用哈希值简化字段对比 #

如果业务字段数量多，写全所有字段太繁琐，可以用HASHBYTES生成每行的哈希值，通过对比哈希值判断是否变更：

WITH yesterday_data AS (
    SELECT 
        *,
        -- 用分隔符拼接所有业务字段，生成哈希值（避免拼接歧义）
        HASHBYTES('SHA2_256', 
            CONCAT(
                ISNULL(COL1, ''), '|',
                ISNULL(COL2, ''), '|',
                /* 其他业务字段，注意都用ISNULL处理NULL */
                ISNULL(COLN, '')
            )
        ) AS row_hash
    FROM your_table
    WHERE BUSINESS_DATE = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
),
today_data AS (
    SELECT 
        *,
        HASHBYTES('SHA2_256', 
            CONCAT(
                ISNULL(COL1, ''), '|',
                ISNULL(COL2, ''), '|',
                /* 其他业务字段 */
                ISNULL(COLN, '')
            )
        ) AS row_hash
    FROM your_table
    WHERE BUSINESS_DATE = CAST(GETDATE() AS DATE)
)
SELECT 
    COALESCE(t.COL1, y.COL1) AS COL1,
    COALESCE(t.COL2, y.COL2) AS COL2,
    -- 其他业务字段
    CASE
        WHEN y.BUSINESS_DATE IS NULL THEN 'NEW'
        WHEN t.row_hash != y.row_hash THEN 'CHANGED'
    END AS STATUS
FROM today_data t
FULL OUTER JOIN yesterday_data y
    ON t.COL1 = y.COL1
    AND t.COL2 = y.COL2
    /* 所有业务字段的关联条件 */
WHERE 
    y.BUSINESS_DATE IS NULL 
    OR t.row_hash != y.row_hash

关键注意事项 #

• 拼接哈希值时必须加分隔符（比如示例中的|），避免不同字段组合拼接后产生歧义（如COL1='AB', COL2='C'和COL1='A', COL2='BC'，不加分隔符会得到相同的拼接结果）。
• 用ISNULL(字段, '')处理NULL值，避免CONCAT自动忽略NULL导致哈希值不准确。
• 确保BUSINESS_DATE是纯日期类型（无时间部分），否则筛选两日数据时会出错。

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