SQL技术求助：如何拼接关联RowID至AssociatedRows列？

需求说明 #

需要将临时表#YourTable中**至少共享一个字段值（column1/column2/column3任意一个）**的RowID，以逗号分隔拼接后存入对应的AssociatedRows列。

示例环境 #

表创建语句 #

CREATE TABLE #YourTable (
    RowID INT PRIMARY KEY,
    column1 VARCHAR(255),
    column2 VARCHAR(255),
    column3 VARCHAR(255),
    AssociatedRows VARCHAR(MAX)
);

测试数据插入 #

INSERT INTO #YourTable (RowID, column1, column2, column3, AssociatedRows)
VALUES
    (1, 'A', 'B', 'C', ''),
    (2, 'D', 'E', 'F', ''),
    (3, 'A', 'X', 'Y', ''),
    (4, 'G', 'H', 'I', ''),
    (5, 'J', 'K', 'C', '');

预期输出 #

RowID | column1| column2| column3| AssociatedRows
----------------------------------------------
1     | A      | B      | C      | 1,3,5
2     | D      | E      | F      | 2
3     | A      | X      | Y      | 1,3,5
4     | G      | H      | I      | 4
5     | J      | K      | C      | 1,3,5

注：RowID 1、3、5因共享column1（A）或column3（C）被归为同一关联组

原代码问题分析 #

你提供的代码针对#Employee表的多字段分组拼接，但核心问题如下：

1. 仅处理同时匹配多个字段的行，不符合“任意一个字段匹配即可关联”的需求
2. 拆分不同字段的关联组后直接拼接，未合并传递关联的集合（比如1和3共享column1，1和5共享column3，需将三者合并为同一组）
3. 未处理单条记录的关联场景（如RowID2、4，关联组仅包含自身）

正确实现方案 #

需先通过传递性找出所有关联的RowID组，再对每组ID进行拼接。以下是基于SQL Server的完整实现：

完整SQL代码 #

WITH AllAssociations AS (
    -- 收集所有共享任意字段的RowID对（含自身）
    SELECT t1.RowID AS SourceID, t2.RowID AS TargetID
    FROM #YourTable t1
    JOIN #YourTable t2 ON t1.column1 = t2.column1
    UNION
    SELECT t1.RowID AS SourceID, t2.RowID AS TargetID
    FROM #YourTable t1
    JOIN #YourTable t2 ON t1.column2 = t2.column2
    UNION
    SELECT t1.RowID AS SourceID, t2.RowID AS TargetID
    FROM #YourTable t1
    JOIN #YourTable t2 ON t1.column3 = t2.column3
),
RecursiveGroups AS (
    -- 递归生成完整关联组（处理传递关联）
    SELECT SourceID, TargetID
    FROM AllAssociations
    UNION ALL
    SELECT rg.SourceID, aa.TargetID
    FROM RecursiveGroups rg
    JOIN AllAssociations aa ON rg.TargetID = aa.SourceID
    WHERE rg.SourceID <> aa.TargetID
),
GroupedRows AS (
    -- 对每组RowID去重、排序后拼接（SQL Server 2017+支持STRING_AGG）
    SELECT 
        SourceID AS RowID,
        STRING_AGG(DISTINCT TargetID, ',') WITHIN GROUP (ORDER BY TargetID) AS AssociatedRows
    FROM RecursiveGroups
    GROUP BY SourceID
)
-- 更新原表的AssociatedRows列
UPDATE t
SET t.AssociatedRows = gr.AssociatedRows
FROM #YourTable t
JOIN GroupedRows gr ON t.RowID = gr.RowID;

-- 验证结果
SELECT * FROM #YourTable;

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

若使用不支持STRING_AGG的版本，将GroupedRows部分替换为以下代码：

GroupedRows AS (
    SELECT 
        SourceID AS RowID,
        STUFF((
            SELECT ',' + CAST(TargetID AS VARCHAR(10))
            FROM (SELECT DISTINCT TargetID FROM RecursiveGroups WHERE SourceID = rg.SourceID) AS sub
            ORDER BY TargetID
            FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS AssociatedRows
    FROM RecursiveGroups rg
    GROUP BY SourceID
)

代码逻辑说明 #

1. AllAssociations：收集所有共享任意字段的RowID对，用UNION去重避免重复记录
2. RecursiveGroups：通过递归处理传递关联（如1→3、1→5，递归后3→5也会被纳入同一组）
3. GroupedRows：对每组RowID去重、排序后拼接成字符串
4. 最后将拼接结果更新到原表的AssociatedRows列

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