MySQL 将结果集转置为一行

柔光的暖阳◎ 2022-12-24 01:53 154阅读 0赞

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

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][scott_sql]

### 文章目录 ###

*  一.需求
 *  二.解决方案
 *   *  2.1 解决方案1
     *  2.2 解决方案2

# 一.需求 #

希望将几个行组中的数据转换成几行中的列，每个原来的行组转换成一行。  
例如，下面的结果集显示了每个部门中员工的数目:

deptno cnt  
10 3  
20 5  
30 6

希望重新设置输出格式，使其结果集看起来如下:  
deptno\_10 deptno\_20 deptno\_30  
3 5 6

# 二.解决方案 #

## 2.1 解决方案1 ##

使用sum汇总、case进行判断

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
     order by 1;

测试记录:

mysql> 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
        ->  order by 1;
    +-----------+-----------+-----------+
    | deptno_10 | deptno_20 | deptno_30 |
    +-----------+-----------+-----------+
    |         3 |         5 |         6 |
    +-----------+-----------+-----------+
    1 row in set (0.00 sec)

## 2.2 解决方案2 ##

使用max及case函数

select max(case when deptno = 10 then empcount else null end) as deptno_10,
           max(case when deptno = 20 then empcount else null end) as deptno_20,
           max(case when deptno = 30 then empcount else null end) as deptno_30
      from (
    select deptno, count(*) as empcount
      from emp
     group by deptno
           ) x;

测试记录

mysql> select max(case when deptno = 10 then empcount else null end) as deptno_10,
        ->        max(case when deptno = 20 then empcount else null end) as deptno_20,
        ->        max(case when deptno = 30 then empcount else null end) as deptno_30
        ->   from (
        -> select deptno, count(*) as empcount
        ->   from emp
        ->  group by deptno
        ->        ) x;
    +-----------+-----------+-----------+
    | deptno_10 | deptno_20 | deptno_30 |
    +-----------+-----------+-----------+
    |         3 |         5 |         6 |
    +-----------+-----------+-----------+
    1 row in set (0.00 sec)

**步骤进行分解**

mysql> -- 根据部门进行分组
    mysql> select deptno, count(*) as empcount
        ->   from emp
        ->  group by deptno;
    +--------+----------+
    | deptno | empcount |
    +--------+----------+
    |     10 |        3 |
    |     20 |        5 |
    |     30 |        6 |
    +--------+----------+
    3 rows in set (0.00 sec)
    
    mysql>
    mysql>
    mysql> -- 使用case语句进行判断
    mysql> select case when deptno = 10 then empcount else null end as deptno_10,
        ->        case when deptno = 20 then empcount else null end as deptno_20,
        ->        case when deptno = 30 then empcount else null end as deptno_30
        ->   from (
        -> select deptno, count(*) as empcount
        ->   from emp
        ->  group by deptno
        ->        ) x;
    +-----------+-----------+-----------+
    | deptno_10 | deptno_20 | deptno_30 |
    +-----------+-----------+-----------+
    |         3 |      NULL |      NULL |
    |      NULL |         5 |      NULL |
    |      NULL |      NULL |         6 |
    +-----------+-----------+-----------+
    3 rows in set (0.00 sec)
    
    mysql>
    mysql> -- 使用max函数求最大值
    mysql> select max(case when deptno = 10 then empcount else null end) as deptno_10,
        ->        max(case when deptno = 20 then empcount else null end) as deptno_20,
        ->        max(case when deptno = 30 then empcount else null end) as deptno_30
        ->   from (
        -> select deptno, count(*) as empcount
        ->   from emp
        ->  group by deptno
        ->        ) x;
    +-----------+-----------+-----------+
    | deptno_10 | deptno_20 | deptno_30 |
    +-----------+-----------+-----------+
    |         3 |         5 |         6 |
    +-----------+-----------+-----------+
    1 row in set (0.00 sec)

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