SQL 判断集合相等

**背景说明：**集合（分组ID，组员），有n个分组，每个分组有n个组员

需求：将所有组员完全一致的分组ID，更新为同一个分组ID。

 

**示例代码实现**
--样例数据
if object_id(N'tempdb..#CityGroupCity',N'U') is not null
DROP TABLE #CityGroupCity
if object_id(N'tempdb..#CityGroup',N'U') is not null
DROP TABLE #CityGroup
 
if object_id(N'tempdb..#Equal',N'U') is not null
DROP TABLE #Equal
 
create table #CityGroupCity(cityGroupID int,city nvarchar(10) )
INSERT INTO #CityGroupCity
VALUES (1, 'A'),
(1, 'B'),
(1, 'C'),
(2, 'A'),
(2, 'B'),
(2, 'C'),
(3, 'A'),
(3, 'B'),
(3, 'C')
 
 
create table #CityGroup(cityGroupID int,Updflg int)
INSERT INTO #CityGroup
VALUES (1, 0),
(2, 0),
(3, 0),
(4, 0),
(5, 0),
(6, 0)
 
 
 
--查出城市相等的组合
SELECT
SP1.cityGroupID AS s1
,SP2.cityGroupID AS s2 INTO #Equal
FROM #CityGroupCity SP1
,#CityGroupCity SP2
WHERE SP1.cityGroupID < SP2.cityGroupID -- 全部组合
AND SP1.city = SP2.city -- 条件1 ：相同城市,相同终端
GROUP BY SP1.cityGroupID
,SP2.cityGroupID
HAVING COUNT(*) = (SELECT
COUNT(*) -- 条件2 ：城市数量一致
FROM #CityGroupCity SP3
WHERE SP3.cityGroupID = SP1.cityGroupID)
AND COUNT(*) = (SELECT
COUNT(*)
FROM #CityGroupCity SP4
WHERE SP4.cityGroupID = SP2.cityGroupID)
 
 
 
--更新相同分组的id
 
DECLARE @s1 varchar(50) , @s2 varchar(50)
 
DECLARE cursor_out CURSOR FOR --定义游标
SELECT
s1
FROM (SELECT
s1
,COUNT(s2) num
FROM #Equal
GROUP BY s1) a
ORDER BY num DESC
 
--select s1 from #Equal ORDER by s1 DESC
OPEN cursor_out
--打开游标
FETCH NEXT FROM cursor_out INTO @s1
WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT *
UPDATE c
SET c.cityGroupID = @s1
,Updflg = 1
FROM #CityGroup c
INNER JOIN #Equal e
ON c.cityGroupID = e.s2
WHERE e.s1 = @s1
AND Updflg = 0
 
UPDATE c
SET Updflg = 1
FROM #CityGroup c
WHERE c.cityGroupID = @s1
AND Updflg = 0
 
FETCH NEXT FROM cursor_out INTO @s1
END
CLOSE cursor_out
--关闭游标
DEALLOCATE cursor_out --释放游标
 
 
SELECT
*
FROM #CityGroup

 

 结果：