SQL Server中重复Clipboard查询结果的格式优化需求

问题背景 #

我有两个数据库表：

• Clipboard：存储剪贴板基础信息
• ClipboardItemMapping：存储剪贴板与条目关联关系，其中ClipboardId是关联Clipboard表的外键

需求是找出所有内容完全相同的剪贴板：即它们包含的条目数量一致，且所有条目的itemGuid完全匹配。

原查询代码 #

我编写的初始查询如下：

INSERT INTO #SharedClipboardsList (ClipboardId, ClipboardGuid, ShareGuid)
    SELECT
        B.Id,
        A.ItemGuid,
        A.ShareGuid
    FROM 
        #SharedItems A
    INNER JOIN 
        Clipboard B ON A.ItemGuid = B.Guid
    WHERE 
        ItemType = 7;

    -- 统计每个剪贴板的条目数量
    INSERT INTO #ClipboardItemCounts (ClipboardId, itemCount)
        SELECT 
            A.clipboardId, COUNT(DISTINCT A.itemGuid) AS itemCount
        FROM 
            clipboardItemMapping A
        INNER JOIN  
            #SharedClipboardsList B ON A.clipboardId = B.ClipboardId
        WHERE 
            A.IsDeleted = 0
        GROUP BY 
            A.clipboardId;

    -- 找出有至少一个共同条目的剪贴板组合
    INSERT INTO #ClipboardItemsCombined (Clipboard1Id, Clipboard2Id)
        SELECT 
            A.clipboardId AS Clipboard1Id, B.clipboardId AS Clipboard2Id
        FROM 
            clipboardItemMapping A
        INNER JOIN 
            clipboardItemMapping B ON A.itemGuid = B.itemGuid
                                   AND A.clipboardId <> B.clipboardId
                                   AND A.clipboardId < B.clipboardId
                                   AND A.IsDeleted = 0
                                   AND B.IsDeleted = 0
        WHERE 
            A.clipboardId IN (SELECT ClipboardId FROM #SharedClipboardsList)
            AND B.ClipboardId IN (SELECT ClipboardId FROM #SharedClipboardsList);

    -- 筛选出条目数量相同的剪贴板组合
    INSERT INTO #ClipboardItemsCountMatch (Clipboard1Id, Clipboard2Id)
        SELECT 
            cc1.clipboardId AS Clipboard1Id, cc2.clipboardId AS Clipboard2Id
        FROM 
            #ClipboardItemCounts cc1
        INNER JOIN 
            #ClipboardItemCounts cc2 ON cc1.clipboardId <> cc2.clipboardId
                                     AND cc1.clipboardId < cc2.clipboardId 
                                     AND cc1.itemCount = cc2.itemCount;

    -- 找出条目完全匹配的剪贴板组合
    INSERT INTO #ClipboardItemsMatch (Clipboard1Id, Clipboard2Id)
        SELECT cic.Clipboard1Id, cic.Clipboard2Id
        FROM #ClipboardItemsCombined cic
        GROUP BY cic.Clipboard1Id, cic.Clipboard2Id
        HAVING COUNT(*) = (SELECT itemCount
                           FROM #ClipboardItemCounts
                           WHERE clipboardId = cic.Clipboard1Id);

    INSERT INTO #DuplicateClipboardsMap (Clipboard1Id, Clipboard2Id)
        SELECT DISTINCT
            c1.Id AS Clipboard1Id, 
            c2.Id AS Clipboard2Id
        FROM 
            clipboard c1
        INNER JOIN 
            clipboard c2 ON c1.Id <> c2.Id
                         AND c1.Id < c2.Id
        INNER JOIN 
            #ClipboardItemsCountMatch cm ON c1.Id = cm.Clipboard1Id 
                                         AND c2.Id = cm.Clipboard2Id
        INNER JOIN 
            #ClipboardItemsMatch ci ON c1.Id = ci.Clipboard1Id 
                                    AND c2.Id = ci.Clipboard2Id
        WHERE 
            c1.IsDeleted = 0 AND c2.IsDeleted = 0;


    SELECT * FROM #DuplicateClipboardsMap;

当前问题 #

这个查询能识别出重复的剪贴板，但输出格式不符合预期：

• 期望输出：按重复组展示，每个组包含所有内容相同的剪贴板ID

  SameClipboards
  {801,809,815}
  {105,118}

• 当前输出：返回两两组合的结果，无法直观看到完整的重复组

  Clipboard1	Clipboard2
  801	809
  801	815
  809	815
  105	118

解决方案：按组聚合重复剪贴板ID #

在原查询基础上，新增递归CTE和聚合逻辑，将同组的剪贴板ID合并为一个列表：

-- 原有的临时表创建和数据插入逻辑保持不变（省略重复代码）
-- ...

-- 新增：生成重复组并聚合ID
WITH DuplicateGroups AS (
    -- 递归关联所有重复的剪贴板，分配组ID
    SELECT 
        Clipboard1Id AS ClipboardId,
        Clipboard1Id AS GroupId
    FROM #DuplicateClipboardsMap
    UNION ALL
    SELECT 
        CASE WHEN d.Clipboard1Id = dg.ClipboardId THEN d.Clipboard2Id ELSE d.Clipboard1Id END AS ClipboardId,
        dg.GroupId
    FROM DuplicateGroups dg
    JOIN #DuplicateClipboardsMap d 
        ON d.Clipboard1Id = dg.ClipboardId OR d.Clipboard2Id = dg.ClipboardId
    WHERE CASE WHEN d.Clipboard1Id = dg.ClipboardId THEN d.Clipboard2Id ELSE d.Clipboard1Id END NOT IN (SELECT ClipboardId FROM DuplicateGroups)
),
UniqueGroups AS (
    -- 去重并排序组内的剪贴板ID
    SELECT 
        GroupId,
        ClipboardId,
        ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY ClipboardId) AS rn
    FROM DuplicateGroups
    GROUP BY GroupId, ClipboardId
),
GroupedClipboards AS (
    -- 聚合组内ID为逗号分隔的字符串（SQL Server 2017+可用STRING_AGG）
    SELECT 
        GroupId,
        STRING_AGG(ClipboardId, ',') WITHIN GROUP (ORDER BY ClipboardId) AS SameClipboards
    FROM UniqueGroups
    GROUP BY GroupId
),
FinalGroups AS (
    -- 过滤重复的组记录
    SELECT 
        SameClipboards,
        ROW_NUMBER() OVER (PARTITION BY SameClipboards ORDER BY GroupId) AS rn
    FROM GroupedClipboards
)
-- 格式化输出为期望的格式
SELECT CONCAT('{', SameClipboards, '}') AS SameClipboards
FROM FinalGroups
WHERE rn = 1;

-- 清理临时表（可选）
DROP TABLE #SharedClipboardsList;
DROP TABLE #ClipboardItemCounts;
DROP TABLE #ClipboardItemsCombined;
DROP TABLE #ClipboardItemsCountMatch;
DROP TABLE #ClipboardItemsMatch;
DROP TABLE #DuplicateClipboardsMap;

兼容低版本SQL Server（2017以下） #

如果使用的是SQL Server 2016及更早版本，替换GroupedClipboards部分为以下代码（用FOR XML PATH实现字符串拼接）：

GroupedClipboards AS (
    SELECT 
        DISTINCT GroupId,
        STUFF(
            (SELECT ',' + CAST(ClipboardId AS VARCHAR(10)) 
             FROM UniqueGroups ug 
             WHERE ug.GroupId = u.GroupId 
             ORDER BY ClipboardId
             FOR XML PATH('')), 1, 1, ''
        ) AS SameClipboards
    FROM UniqueGroups u
)

逻辑说明 #

1. DuplicateGroups递归CTE：通过两两关联关系，把所有属于同一重复组的剪贴板ID归到同一个GroupId下。
2. UniqueGroups：去重组内重复的ID并排序，确保每个ID只出现一次。
3. GroupedClipboards：将同组ID拼接成字符串，方便展示。
4. FinalGroups：过滤掉重复的组记录，避免同一个重复组被多次输出。

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