在 SQL Server 中查找重复项

我的数据库中有两个表

1. Clipboard和
2. ClipboardItemMapping个

每个剪贴板可以包含一个或多个项目.

在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;

    -- Calculate the item count for each clipboard in clipboardItemMapping
    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

    -- Find combinations of clipboards that have at least one common item
    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)

    -- Filter out combinations of clipboards that have the same number of items
    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;

    -- Find combinations of clipboards that have the exact same set of items
    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为801,809,815的相同剪贴板，以及ID为105,118的其他相同剪贴板，则我想要的输出如下:

|SameClipbords |
|{801,809,815} |
|{105,118}     |

但从我共享的查询中，我得到了如下组合输出:

|Clipboard1 | Clipboard2 |
|   801     |   802      |
|   802     |   809      |
|   801     |   809      |