著作权
备注:测试数据库版本为MySQL 8.0
如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本
一.需求
原表数据:
+--------+--------+
| deptno | ename |
+--------+--------+
| 20 | SMITH |
| 30 | ALLEN |
| 30 | WARD |
| 20 | JONES |
| 30 | MARTIN |
| 30 | BLAKE |
| 10 | CLARK |
| 20 | SCOTT |
| 10 | KING |
| 30 | TURNER |
| 20 | ADAMS |
| 30 | JAMES |
| 20 | FORD |
| 10 | MILLER |
+--------+--------+
要求输出为:
| 10 | CLARK,KING,MILLER |
| 20 | SMITH,JONES,SCOTT,ADAMS,FORD |
| 30 | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
二.解决方案
使用MySQL自带的group_concat函数即可
代码:
select deptno,
group_concat(ename order by empno separator ',' ) as emps
from emp
group by deptno
测试记录
mysql> select deptno,
-> group_concat(ename order by empno separator ',' ) as emps
-> from emp
-> group by deptno;
+--------+--------------------------------------+
| deptno | emps |
+--------+--------------------------------------+
| 10 | CLARK,KING,MILLER |
| 20 | SMITH,JONES,SCOTT,ADAMS,FORD |
| 30 | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
+--------+--------------------------------------+
3 rows in set (0.00 sec)
技术文档
热门文章
155-2731-8020