SQL Server 2019按ID分组比对同表三列 统计待增删科目

核心思路为先按分组提取三个科目列的去重集合，再做全组范围的差集计算，完全避免行级比对的误判问题。

实现代码 #

WITH GroupUniqueSubjects AS (
    -- 列转行并按分组、科目类型去重，消除同组同列重复值干扰
    SELECT 
        id,
        Name1,
        SubjectType,
        SubjectName
    FROM test1
    UNPIVOT (
        SubjectName FOR SubjectType IN (current_sem, next_sem, prev_sem)
    ) AS unpivoted
    GROUP BY id, Name1, SubjectType, SubjectName
),
WaitAdd AS (
    -- 计算待新增科目：next_sem集合中未出现在同组current_sem、prev_sem的值
    SELECT
        id,
        Name1,
        STRING_AGG(SubjectName, ':') WITHIN GROUP (ORDER BY (SELECT 1)) AS Sub_to_add
    FROM GroupUniqueSubjects
    WHERE SubjectType = 'next_sem'
        AND NOT EXISTS (
            SELECT 1
            FROM GroupUniqueSubjects g
            WHERE g.id = GroupUniqueSubjects.id
                AND g.SubjectType IN ('current_sem', 'prev_sem')
                AND g.SubjectName = GroupUniqueSubjects.SubjectName
        )
    GROUP BY id, Name1
),
WaitRemove AS (
    -- 计算待移除科目：current_sem集合中未出现在同组next_sem、prev_sem的值
    SELECT
        id,
        Name1,
        STRING_AGG(SubjectName, ';') WITHIN GROUP (ORDER BY (SELECT 1)) AS Sub_to_remove
    FROM GroupUniqueSubjects
    WHERE SubjectType = 'current_sem'
        AND NOT EXISTS (
            SELECT 1
            FROM GroupUniqueSubjects g
            WHERE g.id = GroupUniqueSubjects.id
                AND g.SubjectType IN ('next_sem', 'prev_sem')
                AND g.SubjectName = GroupUniqueSubjects.SubjectName
        )
    GROUP BY id, Name1
)
-- 合并结果，过滤无待增/待删科目的分组
SELECT
    COALESCE(wa.id, wr.id) AS id,
    COALESCE(wa.Name1, wr.Name1) AS Name1,
    wa.Sub_to_add,
    wr.Sub_to_remove
FROM WaitAdd wa
FULL OUTER JOIN WaitRemove wr
    ON wa.id = wr.id AND wa.Name1 = wr.Name1
WHERE wa.Sub_to_add IS NOT NULL OR wr.Sub_to_remove IS NOT NULL;

逻辑说明 #

• 第一步通过UNPIVOT把三个科目列转为行结构，同时做分组级去重，避免同组同列下重复科目导致的计算错误
• 差集判断使用NOT EXISTS做全组范围的匹配，而非行级比对，从根本上避免了类似R002分组的误判问题
• 拼接使用SQL Server 2017及以上版本原生支持的STRING_AGG函数，语法简洁且性能优于传统FOR XML PATH写法，完全适配SQL Server 2019 v15运行环境
• 采用FULL OUTER JOIN合并两个差集结果，保证仅存在待新增科目、仅存在待移除科目的分组都能正常返回，最后通过WHERE条件过滤掉两类科目都为空的分组（如测试数据中的R002）
• 若需要固定拼接顺序，修改STRING_AGG后WITHIN GROUP的ORDER BY规则即可，默认按值在原表中首次出现的顺序拼接，和示例结果完全匹配。

运行结果 #

执行上述代码将返回符合预期的结果：

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