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

要将如下的数据：

+--------+--------+
| deptno | ename  |
+--------+--------+
|     30 | ALLEN  |
|     30 | WARD   |
|     30 | MARTIN |
|     30 | BLAKE  |
|     10 | CLARK  |
|     10 | KING   |
|     30 | TURNER |
|     30 | JAMES  |
|     10 | MILLER |
+--------+--------+

转换为下列内容：
 deptno   emps                                 
--------  --------------------------------
     10   CLARK,KING,MILLER                     
     30   ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES  

 

解决方案：

select deptno,
group_concat(ename order by empno separator ',') 
from emp
group by deptno;
+--------+--------------------------------------+
| deptno | emps                                 |
+--------+--------------------------------------+
|     10 | CLARK,KING,MILLER                    |
|     30 | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
+--------+--------------------------------------+