MySQL 反向转置结果集

最新推荐文章于 2022-02-14 16:22:59 发布

只是甲

最新推荐文章于 2022-02-14 16:22:59 发布

阅读量655

点赞数

分类专栏： # MySQL CookBook MySQL从小工到专家之路文章标签： mysql sql 转置结果集行转列数据分析

本文链接：https://blog.csdn.net/u010520724/article/details/110386260

版权

MySQL从小工到专家之路同时被 2 个专栏收录

181 篇文章 24 订阅

订阅专栏

MySQL CookBook

73 篇文章 3 订阅

订阅专栏

本文介绍了如何在MySQL8.0中将具有多列存储的非规范化数据转换为行存储。通过使用笛卡尔积和CASE函数，可以实现从列deptno_10, deptno_20, deptno_30到行deptno和counts_by_dept的转换。示例SQL脚本展示了具体的转换过程，并给出了测试结果，成功地将数据转换为期望的格式。

摘要由CSDN通过智能技术生成

备注:测试数据库版本为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)