如何编写SQL 基于员工表、部门表、桥接表将员工关联多部门拼接为逗号分隔单列展示
员工-部门多对多关系下的部门信息拼接查询方案
前置业务与表结构说明
- 核心涉及3张表,用于承载员工和部门的多对多关联关系:
- 员工表:存储员工基础信息,包含员工唯一标识
id、员工姓名字段,示例数据:id=1对应员工jon,id=2对应员工jane - 部门表:存储部门基础信息,包含部门唯一标识
id、部门名称字段,示例数据:id=1对应hr部门,id=2对应sales部门 - 关联桥接表:专门维护员工和部门的多对多映射,包含外键字段
emp_id(关联员工表id)、dept_id(关联部门表id),示例数据:emp_id=1关联dept_id=1,emp_id=2同时关联dept_id=1、dept_id=2
- 员工表:存储员工基础信息,包含员工唯一标识
预期查询效果:单条员工记录对应一行结果,将该员工关联的所有部门名称在同一列中用逗号分隔拼接展示。即最终输出结果为:
- jon → hr
- jane → hr,sales
实现逻辑说明
该需求的核心是按员工维度分组,对关联查询出的部门名称做行转列聚合拼接,不同数据库的内置聚合拼接函数存在语法差异,对应实现SQL如下:
注:以下SQL中出现的表名(employee、dept、emp_dept_bridge)为示例命名,实际使用时替换为业务库中的真实表名即可。
不同数据库的对应写法
- MySQL 5.x/8.x 版本:使用内置
GROUP_CONCAT聚合函数
SELECT e.id, e.emp_name, GROUP_CONCAT(d.dept_name SEPARATOR ',') AS dept_list FROM employee e -- 关联桥接表获取员工对应的部门映射关系 LEFT JOIN emp_dept_bridge b ON e.id = b.emp_id -- 关联部门表获取部门实际名称 LEFT JOIN dept d ON b.dept_id = d.id -- 按员工维度分组,聚合该员工下的所有关联部门 GROUP BY e.id, e.emp_name;
注意:MySQL默认GROUP_CONCAT的结果长度上限为1024字节,如果业务中拼接的部门名称总长度较长,需要提前调整group_concat_max_len系统参数,避免拼接内容被截断。
- PostgreSQL、SQLite 、MySQL 8.0+ 兼容写法:使用标准
STRING_AGG聚合函数
SELECT e.id, e.emp_name, STRING_AGG(d.dept_name, ',') AS dept_list FROM employee e LEFT JOIN emp_dept_bridge b ON e.id = b.emp_id LEFT JOIN dept d ON b.dept_id = d.id GROUP BY e.id, e.emp_name;
- SQL Server 2017及以上版本:同样支持
STRING_AGG函数,语法和PostgreSQL版本基本一致
SELECT e.id, e.emp_name, STRING_AGG(d.dept_name, ',') AS dept_list FROM employee e LEFT JOIN emp_dept_bridge b ON e.id = b.emp_id LEFT JOIN dept d ON b.dept_id = d.id GROUP BY e.id, e.emp_name;
实用优化提示
- 如果桥接表存在重复关联的脏数据,需要在聚合函数中搭配
DISTINCT关键字去重,比如将GROUP_CONCAT(d.dept_name SEPARATOR ',')修改为GROUP_CONCAT(DISTINCT d.dept_name SEPARATOR ','),避免拼接出重复的部门名称 - 分组时建议同时带上员工id和员工姓名做分组依据,避免存在同名员工时出现数据聚合错误
内容的提问来源于stack exchange,提问作者johnmills
相关产品推荐
相关产品推荐

