SQL多表关联：GROUP_CONCAT结合LEFT JOIN实现目标数据展示

涉及的四张表结构 #

• 主表licence：存储许可证基础数据，核心字段licence_id、owner_id

+------------+----------------+ 
| licence_id | owner_id       |
+------------+----------------+ 
| 1          |            124 |

• 关联表licence_countries：关联许可证与对应国家

+------------+----------------+ 
| licence_id | country_id     |
+------------+----------------+ 
| 1          |            45  |

• 关联表region_countries：存储国家所属的地区

+------------+----------------+ 
| region_id  |   country_id   |
+------------+----------------+ 
| 45         |            10  |
| 45         |            12  |

• 多语言表license_language：存储许可证的多语言标题与描述

+------------+----------------+----------+---------------+
| licence_id  |   language_id |   title  | description   |
+------------+----------------+----------+---------------+
| 10         |            18  | Licence  | Licence for.. |
| 10         |            13  | Licenz   | Licenz fur..  |

核心需求 #

1. 按licence_id将对应的国家、地区用分号拼接成字符串
2. 关联多语言表时保留LEFT JOIN原生效果：一个许可证对应N种语言则生成N行记录，拼接好的国家/地区字段在每行重复显示
3. 同时需要获取licence表的其他字段

现有问题 #

单独处理拼接的查询可以正常运行：

SELECT
    l.id,
    GROUP_CONCAT(rc.id_country SEPARATOR ';') AS countries,
    GROUP_CONCAT(rc.id_region SEPARATOR  ';') AS regions
FROM license l
LEFT JOIN licence_country lc ON l.id = lc.id_licence
LEFT JOIN region_country rc ON lc.id_country = rc.id_country
GROUP BY l.id

但加入多语言表的LEFT JOIN后：

LEFT JOIN license_language ll ON l.id = ll.id_licence

若将title、language_id等字段加入GROUP BY，会导致多语言的多条记录被合并，无法满足需求。期望最终结果如下：

+------------+----------------+----------+---------------+--------------+----------+
| licence_id |   language_id  |   title  | description   | countries    | regions  |
+------------+----------------+----------+---------------+--------------+----------+
| 10         |            18  | Licence  | Licence for.. |France, Bel.. | Europe.. |
| 10         |            13  | Licenz   | Licenz fur..  |France, Bel.. | Europe.. |
| 13         |            10  | Licence  | Licence for.. |Brazil, Arg.. | South .. |
| 15         |            5   | Lice     | Lice f. ir..  |USA, Canada   | North .. |

解决方案 #

核心思路是先对每个许可证预先生成拼接好的国家/地区字符串，再关联多语言表，避免多语言表的多行数据干扰GROUP_CONCAT结果。

方法1：子查询预计算拼接字段 #

适用于所有MySQL版本：

SELECT
    l.licence_id,
    l.owner_id, -- 可添加licence表其他需要的字段
    ll.language_id,
    ll.title,
    ll.description,
    concat_data.countries,
    concat_data.regions
FROM licence l
-- 预计算每个许可证的拼接字段
LEFT JOIN (
    SELECT
        lc.licence_id,
        GROUP_CONCAT(DISTINCT rc.country_id SEPARATOR ';') AS countries,
        GROUP_CONCAT(DISTINCT rc.region_id SEPARATOR ';') AS regions
    FROM licence_countries lc
    LEFT JOIN region_countries rc ON lc.country_id = rc.country_id
    GROUP BY lc.licence_id
) concat_data ON l.licence_id = concat_data.licence_id
-- 关联多语言表，保留多行记录
LEFT JOIN license_language ll ON l.licence_id = ll.licence_id
ORDER BY l.licence_id, ll.language_id;

方法2：窗口函数（MySQL 8.0+支持） #

利用窗口函数直接按licence_id分区计算拼接值，无需子查询：

SELECT
    DISTINCT
    l.licence_id,
    l.owner_id,
    ll.language_id,
    ll.title,
    ll.description,
    GROUP_CONCAT(DISTINCT rc.country_id SEPARATOR ';') OVER (PARTITION BY l.licence_id) AS countries,
    GROUP_CONCAT(DISTINCT rc.region_id SEPARATOR ';') OVER (PARTITION BY l.licence_id) AS regions
FROM licence l
LEFT JOIN licence_countries lc ON l.licence_id = lc.licence_id
LEFT JOIN region_countries rc ON lc.country_id = rc.country_id
LEFT JOIN license_language ll ON l.licence_id = ll.licence_id
ORDER BY l.licence_id, ll.language_id;

关键说明 #

• 两种方法都确保先完成licence_id维度的拼接，再关联多语言表，保证多语言每行记录复用同一许可证的拼接结果
• 添加DISTINCT可避免因关联逻辑导致的重复拼接值（如一个国家对应多个地区时的重复数据）

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