SET @enames='';
SET @last_deptno=-1;
SELECT deptno, MAX(enames) AS enames
FROM (SELECT deptno,
IF(@last_deptno = deptno, @rn := @rn + 1, @rn := 1) AS rn,
IF(@last_deptno = deptno,
@enames := concat(@enames, ',', ename),
@enames := ename) AS enames,
@last_deptno := deptno AS last_deptno
FROM emp
ORDER BY deptno, empno)a
GROUP BY deptno;
+--------+--------------------------------------+
| deptno | enames |
+--------+--------------------------------------+
| 10 | CLARK,KING,MILLER |
| 20 | SMITH,JONES,SCOTT,ADAMS,FORD |
| 30 | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
+--------+--------------------------------------+
3 rows in set (0.01 sec)
mysql之wm_concat
最新推荐文章于 2023-06-09 17:57:38 发布