我已经使用您提供的数据进行了测试,它可以正常工作。
第一个是不包含在另一个组中的组:
SELECT DISTINCT Group_Number FROM #T
WHERE NOT EXISTS (SELECT Group_Number G2
FROM #T AS T2
WHERE T2.Group_Number <> #t.Group_Number
AND T2.ID = #T.ID)
另一种方法非常简单,只需使用以下代码:
SELECT DISTINCT Group_Number FROM #T WHERE NOT Group_Number IN (
SELECT DISTINCT Group_Number FROM #T
WHERE NOT EXISTS (SELECT Group_Number G2
FROM #T AS T2
WHERE T2.Group_Number <> #t.Group_Number
AND T2.ID = #T.ID)
)
只是问自己,我意识到我的回答并不完全准确。
首先,我意识到添加以下内容:
INSERT INTO #t
VALUES (6, 50),
(7, 60),
(8, 50),
(8, 60)
第8组没有出现,因为其中一个项目在第6组中,另一个项目在第7组中。因此,我进行了大量检查并得出结论,以下代码可以保证结果,并提供可追溯性以验证响应是否正确:
SELECT DISTINCT Group_Number FROM
(
SELECT T1.Group_Number, T1.Rows, T2.Group_Number as Comparing_With_Other_Group, Count(DISTINCT T2.ID) AS Rows_On_Other_Group
FROM (
SELECT Group_Number, Count(DISTINCT ID) AS Rows
FROM #T
GROUP BY Group_Number
) T1
INNER JOIN #T AS T2
ON T1.Group_Number <> T2.Group_Number
AND EXISTS (SELECT 1 FROM #T WHERE #T.Group_Number = T1.Group_Number and #T.ID = T2.ID)
GROUP BY T1.Group_Number, T2.Group_Number, T1.Rows
) SubQry
WHERE Rows = Rows_On_Other_Group
如果你只运行SubQry,你将看到可追溯性,而完整的查询将向你展示系统可以找到过滤所搜寻组的ID的另一个组的群组,在这个群组中,找到相同数量的ID。