如何编写SQL查询将多地址ID列转为对应标题拼接列

我有一张user表，其中ID_adress列存储了另一张地址表的多个ID，表结构如下：

user表

另有一张存储地址的address表：

address表

需要编写SQL查询，得到如下格式的结果集：

目标结果

不同数据库的实现方式略有差异，以下是主流数据库的实现方案：

MySQL（8.0及以上版本） #

使用JSON_TABLE拆分逗号分隔的ID，再通过GROUP_CONCAT聚合地址名称：

SELECT 
    u.ID,
    GROUP_CONCAT(a.title ORDER BY a.ID SEPARATOR ',') AS title_ADR
FROM 
    user u
JOIN 
    JSON_TABLE(
        CONCAT('["', REPLACE(u.ID_adress, ',', '","'), '"]'),
        '$[*]' COLUMNS (addr_id INT PATH '$')
    ) jt ON a.ID = jt.addr_id
JOIN 
    address a ON a.ID = jt.addr_id
GROUP BY 
    u.ID;

如果是MySQL 5.x版本，没有JSON_TABLE，可以用递归CTE来拆分：

WITH RECURSIVE split_ids AS (
    SELECT 
        ID,
        ID_adress,
        SUBSTRING_INDEX(ID_adress, ',', 1) AS addr_id,
        SUBSTRING(ID_adress, LOCATE(',', ID_adress) + 1) AS remaining
    FROM user
    WHERE ID_adress IS NOT NULL AND ID_adress != ''
    UNION ALL
    SELECT 
        ID,
        ID_adress,
        SUBSTRING_INDEX(remaining, ',', 1) AS addr_id,
        SUBSTRING(remaining, LOCATE(',', remaining) + 1) AS remaining
    FROM split_ids
    WHERE remaining IS NOT NULL AND remaining != ''
)
SELECT 
    s.ID,
    GROUP_CONCAT(a.title ORDER BY a.ID SEPARATOR ',') AS title_ADR
FROM split_ids s
JOIN address a ON a.ID = s.addr_id
GROUP BY s.ID;

SQL Server #

使用STRING_SPLIT拆分ID，再用STRING_AGG聚合：

SELECT 
    u.ID,
    STRING_AGG(a.title, ',') WITHIN GROUP (ORDER BY a.ID) AS title_ADR
FROM user u
CROSS APPLY STRING_SPLIT(u.ID_adress, ',') AS split
JOIN address a ON a.ID = CAST(split.value AS INT)
GROUP BY u.ID;

PostgreSQL #

使用regexp_split_to_table拆分ID，再用STRING_AGG聚合：

SELECT 
    u.ID,
    STRING_AGG(a.title, ',' ORDER BY a.ID) AS title_ADR
FROM user u
JOIN regexp_split_to_table(u.ID_adress, ',') AS split(addr_id)
ON a.ID = split.addr_id::INT
JOIN address a ON a.ID = split.addr_id::INT
GROUP BY u.ID;

这种将多个ID存储在单个字段的设计属于反范式设计，会导致查询效率低下、难以维护，建议使用中间关联表（如user_address）来存储用户与地址的多对多关系，表结构示例：

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