MySQL 反向转置结果集

红太狼 2022-12-25 03:00 162阅读 0赞

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

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

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