MySQL 反向转置结果集

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

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

scott建表及录入数据sql脚本

一.需求

把列转换为行。

±----------±----------±----------+

| deptno_10 | deptno_20 | deptno_30 |

±----------±----------±----------+

| 3 | 5 | 6 |

±----------±----------±----------+

要把它转换为:

±-------±---------------+

| deptno | counts_by_dept |

±-------±---------------+

| 10 | 3 |

| 20 | 5 |

| 30 | 6 |

±-------±---------------+

二.解决方案

检验想要的结果集，很容易明白，对表EMP执行简单的count和group by，就能产生想要的结果。

尽管如此，这里的目的是假设数据按行存储，也许数据是以多列存储、非规范化和聚集过的值。

用笛卡尔积加上case函数即可

select dept.deptno,
       case dept.deptno
            when 10 then emp_cnts.deptno_10
            when 20 then emp_cnts.deptno_20
            when 30 then emp_cnts.deptno_30
       end as counts_by_dept
  from (
select sum(case when deptno = 10 then 1 else 0 end) as deptno_10,
       sum(case when deptno = 20 then 1 else 0 end) as deptno_20,
       sum(case when deptno = 30 then 1 else 0 end) as deptno_30
  from emp
       ) emp_cnts,
       ( select deptno from dept where deptno <= 30) dept
;
           

测试记录:

mysql> select dept.deptno,
    ->        case dept.deptno
    ->             when 10 then emp_cnts.deptno_10
    ->             when 20 then emp_cnts.deptno_20
    ->             when 30 then emp_cnts.deptno_30
    ->        end as counts_by_dept
    ->   from (
    -> select sum(case when deptno = 10 then 1 else 0 end) as deptno_10,
    ->        sum(case when deptno = 20 then 1 else 0 end) as deptno_20,
    ->        sum(case when deptno = 30 then 1 else 0 end) as deptno_30
    ->   from emp
    ->        ) emp_cnts,
    ->        ( select deptno from dept where deptno <= 30) dept;
+--------+----------------+
| deptno | counts_by_dept |
+--------+----------------+
|     10 |              3 |
|     20 |              5 |
|     30 |              6 |
+--------+----------------+
3 rows in set (0.00 sec)