MySQL 把结果集转置为多行

浅浅的花香味﹌ 2022-12-24 07:58 221阅读 0赞

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

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][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
    ;

测试记录:

[scott_sql]: https://www.jianshu.com/p/532fe68924cb