Microsoft SQL Server 2019：关联多表处理逗号分隔值的SQL查询问题

场景与数据说明 #

现有三个包含逗号分隔值的表，需要将各表中的值展开关联后合并成指定结果：

• Table1：Values列包含自有值、Table2的名称、Table3的名称
• Table2：存储名称对应的具体值集合
• Table3：存储关联的Table2名称集合

各表数据 #

Table1

Table2

Table3

期望结果 #

原SQL问题分析 #

你提供的SQL存在以下问题：

• 拆分Table1时错误引用了data字段，实际应为Values列
• 关联逻辑混乱，未分层处理Table3到Table2的嵌套关联，无法正确展开Table3对应的所有Table2值
• 未对拆分后的数据进行聚合，无法得到合并后的单行结果

正确SQL实现方案 #

以下是基于SQL Server的实现（需SQL Server 2017及以上版本，依赖STRING_SPLIT和STRING_AGG函数）：

WITH SplitTable1 AS (
    -- 拆分Table1的Values列，得到每个独立项
    SELECT 
        t1.ID,
        t1.Name,
        TRIM(s.value) AS Item
    FROM Table1 t1
    CROSS APPLY STRING_SPLIT(t1.[Values], ',') s
),
ResolvedItems AS (
    -- 处理所有项：自有值直接保留，Table2/Table3项展开为具体值
    SELECT 
        st1.ID,
        st1.Name,
        -- 优先取Table2拆分值，再取Table3关联的Table2拆分值，最后保留原自有值
        COALESCE(t2_split.value, t3_t2_split.value, st1.Item) AS FinalValue
    FROM SplitTable1 st1
    -- 关联Table2并拆分其Values
    LEFT JOIN Table2 t2 ON st1.Item = t2.Name
    LEFT JOIN STRING_SPLIT(t2.[Values], ',') t2_split ON 1=1
    -- 关联Table3，再关联对应Table2并拆分其Values
    LEFT JOIN Table3 t3 ON st1.Item = t3.Name
    LEFT JOIN STRING_SPLIT(t3.[Values], ',') t3_split ON 1=1
    LEFT JOIN Table2 t3_t2 ON t3_split.value = t3_t2.Name
    LEFT JOIN STRING_SPLIT(t3_t2.[Values], ',') t3_t2_split ON 1=1
    -- 过滤空值，确保所有有效项被保留
    WHERE COALESCE(t2_split.value, t3_t2_split.value, st1.Item) IS NOT NULL
)
-- 按ID和Name分组，合并所有值为逗号分隔字符串
SELECT 
    ID,
    Name,
    STRING_AGG(FinalValue, ',') AS [Values]
FROM ResolvedItems
GROUP BY ID, Name
ORDER BY ID;

逻辑说明 #

1. SplitTable1：将Table1的Values列拆分为独立行，每个值对应一行数据
2. ResolvedItems：
   • 若拆分项是Table2名称，直接拆分Table2对应的Values得到具体值
   • 若拆分项是Table3名称，先拆分Table3的Values得到关联的Table2名称，再拆分这些Table2的Values得到具体值
   • 若拆分项是自有值，直接保留原内容
3. 最终聚合：按ID和Name分组，使用STRING_AGG将所有展开后的具体值合并为逗号分隔的字符串，得到目标结果

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