SQL多表关联查询：将分隔符连接的ID替换为对应描述

嘿，我来帮你搞定这个需求！你要把table2里用;分隔的table1_id字段，转换成table1中对应的desc内容，对吧？下面针对几种常见数据库给出具体的实现方案：

MySQL/MariaDB 方案 #

我们可以通过拆分字符串、关联查询再合并结果的方式实现：

SELECT 
    t2.id,
    GROUP_CONCAT(t1.desc SEPARATOR ';') AS desc_list
FROM table2 t2
LEFT JOIN (
    -- 拆分table2的table1_id为单独的ID行
    SELECT 
        t2.id AS table2_id,
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(t2.table1_id, ';', numbers.n), ';', -1) AS UNSIGNED) AS table1_id
    FROM table2 t2
    -- 生成足够多的数字序列来覆盖最多的分号分隔数（这里假设最多10个，可按需调整）
    INNER JOIN (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
        UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    ) numbers
    ON CHAR_LENGTH(t2.table1_id) - CHAR_LENGTH(REPLACE(t2.table1_id, ';', '')) >= numbers.n - 1
) split_ids ON t2.id = split_ids.table2_id
LEFT JOIN table1 t1 ON t1.id = split_ids.table1_id
GROUP BY t2.id;

说明：

• 子查询numbers生成数字序列，用来拆分多值ID，你可以根据实际最大分隔数量调整数字个数
• SUBSTRING_INDEX配合数字序列把多值ID拆成单独行，再关联table1拿到对应描述
• 最后用GROUP_CONCAT把同一个table2行的描述合并回分号分隔的字符串

PostgreSQL 方案 #

PostgreSQL有更简洁的字符串处理函数：

SELECT 
    t2.id,
    STRING_AGG(t1.desc, ';' ORDER BY pos) AS desc_list
FROM table2 t2
LEFT JOIN LATERAL (
    -- 拆分字符串并保留顺序
    SELECT 
        UNNEST(STRING_TO_ARRAY(t2.table1_id, ';'))::INT AS table1_id,
        GENERATE_SUBSCRIPTS(STRING_TO_ARRAY(t2.table1_id, ';'), 1) AS pos
) split_ids ON true
LEFT JOIN table1 t1 ON t1.id = split_ids.table1_id
GROUP BY t2.id;

说明：

• STRING_TO_ARRAY把分号分隔的ID转成数组，UNNEST把数组拆成单独行
• GENERATE_SUBSCRIPTS用来保留原ID的顺序，确保合并后的描述顺序和原ID一致
• STRING_AGG负责合并结果

SQL Server 方案（2016及以上版本） #

SQL Server 2016+支持STRING_SPLIT函数：

SELECT 
    t2.id,
    STRING_AGG(t1.desc, ';') WITHIN GROUP (ORDER BY split_ids.ordinal) AS desc_list
FROM table2 t2
LEFT JOIN STRING_SPLIT(t2.table1_id, ';', 1) split_ids ON true
LEFT JOIN table1 t1 ON t1.id = CAST(split_ids.value AS INT)
GROUP BY t2.id;

说明：

• STRING_SPLIT的第三个参数1用来返回拆分后的序号，保证顺序和原ID一致
• CAST(split_ids.value AS INT)把拆分后的字符串ID转成整数，关联table1
• STRING_AGG合并结果并保持顺序

小提示 #

如果你的数据库版本比较旧，没有这些内置拆分函数，可以考虑自定义拆分函数，但优先用内置函数，性能会更好哦！

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