如何拆分TABLE B的A_ID并关联TABLE A实现指定SQL查询结果

现有两张表结构及数据如下：

表结构与数据 #

TABLE A #

TABLE B #

需求是拆分TABLE B中的A_ID字段（逗号分隔的ID列表），关联TABLE A获取对应的Code，再将同一TABLE B记录的Code聚合为逗号分隔的字符串，最终得到如下结果：

以下是不同主流数据库的实现方案：

MySQL 实现 #

方案1（MySQL 8.0+，推荐）

利用JSON_TABLE拆分字符串，结合GROUP_CONCAT聚合结果：

SELECT 
    b.ID,
    GROUP_CONCAT(a.Code ORDER BY a.ID SEPARATOR ', ') AS Code
FROM TABLE_B b
JOIN JSON_TABLE(
    CONCAT('["', REPLACE(b.A_ID, ',', '","'), '"]'),
    '$[*]' COLUMNS (a_id INT PATH '$')
) j
JOIN TABLE_A a ON j.a_id = a.ID
GROUP BY b.ID
ORDER BY b.ID;

方案2（MySQL 5.7兼容）

用递归CTE模拟拆分字符串：

WITH RECURSIVE cte AS (
    SELECT 
        ID,
        A_ID,
        1 AS start_pos,
        LOCATE(',', A_ID) AS end_pos
    FROM TABLE_B
    UNION ALL
    SELECT 
        ID,
        A_ID,
        end_pos + 1,
        LOCATE(',', A_ID, end_pos + 1)
    FROM cte
    WHERE end_pos > 0
)
SELECT 
    c.ID,
    GROUP_CONCAT(a.Code ORDER BY a.ID SEPARATOR ', ') AS Code
FROM cte c
JOIN TABLE_A a ON 
    SUBSTRING(
        c.A_ID, 
        c.start_pos, 
        CASE WHEN c.end_pos = 0 THEN LENGTH(c.A_ID) ELSE c.end_pos - c.start_pos END
    ) = a.ID
GROUP BY c.ID
ORDER BY c.ID;

SQL Server 实现（2017+） #

用STRING_SPLIT拆分字符串，STRING_AGG聚合结果：

SELECT 
    b.ID,
    STRING_AGG(a.Code, ', ') WITHIN GROUP (ORDER BY a.ID) AS Code
FROM TABLE_B b
JOIN STRING_SPLIT(b.A_ID, ',') s
JOIN TABLE_A a ON TRY_CAST(s.value AS INT) = a.ID
GROUP BY b.ID
ORDER BY b.ID;

Oracle 实现 #

用REGEXP_SUBSTR拆分字符串，LISTAGG聚合结果：

SELECT 
    b.ID,
    LISTAGG(a.Code, ', ') WITHIN GROUP (ORDER BY a.ID) AS Code
FROM TABLE_B b
JOIN TABLE_A a ON a.ID IN (
    SELECT TRY_CAST(REGEXP_SUBSTR(b.A_ID, '[^,]+', 1, LEVEL) AS INT)
    FROM DUAL
    CONNECT BY REGEXP_SUBSTR(b.A_ID, '[^,]+', 1, LEVEL) IS NOT NULL
)
GROUP BY b.ID
ORDER BY b.ID;

注意事项 #

• 以上方案默认A_ID中的值均为TABLE A的有效ID，若需处理非法ID，可通过TRY_CAST非空判断等逻辑过滤无效关联。
• 从数据库设计规范来看，TABLE B用逗号分隔存储关联ID属于反范式设计，建议改为创建中间关联表（如TABLE_B_A，记录B.ID与A.ID的一对一关联），既能提升查询效率，也能避免数据一致性问题。

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