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)