SQL Server中逗号分隔聚合键的两表关联查询如何实现

适用版本：SQL Server 2016 及以上 #

核心使用内置STRING_SPLIT函数拆分多值字段后关联，语句如下：

SELECT 
    t1.AGGREGATE_KEY,
    t1.Value_1,
    t2.Value_2
FROM table1 t1
-- 拆分AGGREGATE_KEY逗号分隔值为单行单值
CROSS APPLY STRING_SPLIT(t1.AGGREGATE_KEY, ',') AS split_result
INNER JOIN table2 t2 
    ON t2.KEY = split_result.value

说明 #

• 若需要保留table1中无匹配记录的行，将INNER JOIN替换为LEFT JOIN即可
• 若拆分后的key存在前后空格，可修改关联条件为ON t2.KEY = LTRIM(RTRIM(split_result.value))避免匹配失败

适用版本：SQL Server 2016 以下 #

无STRING_SPLIT函数时，使用XML转换实现拆分，语句如下：

SELECT 
    t1.AGGREGATE_KEY,
    t1.Value_1,
    t2.Value_2
FROM (
    -- 转换逗号分隔值为XML格式后拆分
    SELECT 
        AGGREGATE_KEY,
        Value_1,
        LTRIM(RTRIM(xml_node.value('.[1]','varchar(8000)'))) AS split_key
    FROM (
        SELECT 
            AGGREGATE_KEY,
            Value_1,
            CAST('<root><item>' + REPLACE(AGGREGATE_KEY,',','</item><item>') + '</item></root>' AS XML) AS split_xml
        FROM table1
    ) temp_table
    CROSS APPLY split_xml.nodes('/root/item') AS xml_result(xml_node)
) t1
INNER JOIN table2 t2 
    ON t2.KEY = t1.split_key

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