在SQL Server中合并桥接表的多对多行组合以缩减行数

1. 生成去重后的bridge_area表 #

从旧的Person-Area桥接表中提取唯一的区域组合，为每个组合分配唯一的Key_Bridge标识。利用聚合函数确保相同区域组合生成一致的标识字符串，以此作为去重依据。

适用于SQL Server 2017及以上版本（使用STRING_AGG） #

-- 创建新的bridge_area表，存储唯一区域组合
CREATE TABLE bridge_area (
    Key_Bridge INT IDENTITY(1,1) PRIMARY KEY,
    Area_Combination NVARCHAR(MAX) NOT NULL, -- 排序后的区域ID拼接字符串，保证同一组合唯一
    UNIQUE(Area_Combination) -- 约束避免重复组合
);

-- 提取唯一区域组合并插入新表
WITH Person_Area_Groups AS (
    SELECT 
        PersonID,
        STRING_AGG(AreaID, ',') WITHIN GROUP (ORDER BY AreaID) AS Area_Combination
    FROM Person_Area -- 旧的Person-Area桥接表
    GROUP BY PersonID
)
INSERT INTO bridge_area (Area_Combination)
SELECT DISTINCT Area_Combination
FROM Person_Area_Groups;

适用于SQL Server 2016及以下版本（使用XML路径拼接） #

WITH Person_Area_Groups AS (
    SELECT 
        PersonID,
        STUFF(
            (SELECT ',' + CAST(AreaID AS NVARCHAR(10)) 
             FROM Person_Area pa2 
             WHERE pa2.PersonID = pa1.PersonID
             ORDER BY AreaID
             FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
            1, 1, ''
        ) AS Area_Combination
    FROM Person_Area pa1
    GROUP BY PersonID
)
INSERT INTO bridge_area (Area_Combination)
SELECT DISTINCT Area_Combination
FROM Person_Area_Groups;

2. 填充新Person表的Key_Bridge字段 #

假设新Person表New_Person已包含原Person表的基础信息（如PersonID、Address等），通过关联区域组合匹配新bridge_area表的标识：

-- 为新Person表添加Key_Bridge字段（若未添加）
ALTER TABLE New_Person ADD Key_Bridge INT;

-- 关联更新Key_Bridge
WITH Person_Area_Groups AS (
    SELECT 
        PersonID,
        STRING_AGG(AreaID, ',') WITHIN GROUP (ORDER BY AreaID) AS Area_Combination
    FROM Person_Area
    GROUP BY PersonID
)
UPDATE np
SET np.Key_Bridge = ba.Key_Bridge
FROM New_Person np
JOIN Person_Area_Groups pag ON np.PersonID = pag.PersonID
JOIN bridge_area ba ON pag.Area_Combination = ba.Area_Combination;

性能优化提示 #

• 提前为旧桥接表Person_Area的PersonID、AreaID字段创建非聚集索引，加速分组聚合操作。
• 处理超大规模数据时，可先将Person_Area_Groups的结果存入临时表，避免重复计算。
• 操作前备份原数据，先在测试环境验证逻辑后再应用到生产环境。

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