mysql 把一列转多行_MySQL 把结果集转置为多行

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:

scott建表及录入数据sql脚本

一.需求

要把行转换为列，根据原表给定列的每个值创建一个列。

例如，返回每个员工及他们的职位(JOB)，目前的查询返回如下结果集:

希望重新设置为结果集的格式，使每个职位使用一列:

二.解决方案

要解决本例的问题，必须使每个job/ename组合唯一，然后，在使用聚集函数去除null时，不会丢失ename。

使用标量子查询，按empno给每个员工分等级。

使用CASE表达式和聚集函数MAX对结果集进行转置变换，同事按子查询的返回值分组:

select max(case when job = 'CLERK'

then ename else null end) as clerks,

max(case when job = 'ANALYST'

then ename else null end) as analysts,

max(case when job = 'MANAGER'

then ename else null end) as mgrs,

max(case when job = 'PRESIDENT'

then ename else null end) as prez,

max(case when job = 'SALESMAN'

then ename else null end) as sales

FROM (

SELECT e.job,

e.ename,

(select count(*) from emp d

where e.job = d.job and e.empno < d.empno) as rnk

from emp e

) x

group by rnk

order by rnk

;

测试记录:

mysql> select max(case when job = 'CLERK'

-> then ename else null end) as clerks,

-> max(case when job = 'ANALYST'

-> then ename else null end) as analysts,

-> max(case when job = 'MANAGER'

-> then ename else null end) as mgrs,

-> max(case when job = 'PRESIDENT'

-> then ename else null end) as prez,

-> max(case when job = 'SALESMAN'

-> then ename else null end) as sales

-> FROM (

-> SELECT e.job,

-> e.ename,

-> (select count(*) from emp d

-> where e.job = d.job and e.empno < d.empno) as rnk

-> from emp e

-> ) x

-> group by rnk

-> order by rnk

-> ;

+--------+----------+-------+------+--------+

| clerks | analysts | mgrs | prez | sales |

+--------+----------+-------+------+--------+

| MILLER | FORD | CLARK | KING | TURNER |

| JAMES | SCOTT | BLAKE | NULL | MARTIN |

| ADAMS | NULL | JONES | NULL | WARD |

| SMITH | NULL | NULL | NULL | ALLEN |

+--------+----------+-------+------+--------+

4 rows in set (0.00 sec)