mysql根据分隔_MySQL 根据表中的行创建一个分隔列表

备注:测试数据库版本为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)