mysql 行转列 case when

亦凉 2024-03-17 00:02 58阅读 0赞

**目录**

省流

group by 搭配 sum(case when..then..end) as field

一、准备工作

1.建表

2.数据

3.查询结果

二、需求

三、sql写法

group by 搭配 max(case when..then..end) as field

一、准备工作

1.建表

2.数据

3.查询结果

二、需求

三、sql写法

1.0版本

2.0版本

3.0版本

--------------------

### 省流 ###

**使用 group by 搭配 sum(case when) 实现行转列**

**使用 group by 搭配 max(case when) 实现行转列**

## group by 搭配 sum(case when..then..end) as field ##

### 一、准备工作 ###

#### 1.建表 ####

CREATE TABLE `energy_consume` (
      `factory` varchar(100) DEFAULT NULL,
      `energy_type` varchar(100) DEFAULT NULL,
      `value` decimal(10,2) DEFAULT NULL
    )

#### 2.数据 ####

INSERT INTO energy_consume
    (factory, energy_type, value)
    VALUES
    ('t1', '0', 10.20),
    ('t1', '1', 12.20),
    ('t1', '2', 13.20),
    ('t1', '0', 13.10),
    ('t2', '0', 5.10),
    ('t2', '0', 6.10),
    ('t2', '1', 8.10),
    ('t6', '1', 20.10);

#### 3.查询结果 ####

factory|energy_type|value|
    -------+-----------+-----+
    t1     |0          |10.20|
    t1     |1          |12.20|
    t1     |2          |13.20|
    t1     |0          |13.10|
    t2     |0          | 5.10|
    t2     |0          | 6.10|
    t2     |1          | 8.10|
    t6     |1          |20.10|

### 二、需求 ###

现在希望能把不同类型的能源作为列名，将能源按照厂区来分组，将同一类型的能源的值加和。如下：

厂区    |电   |水   |气  |
    -------+-----+-----+-----+
    t1     |23.30|12.20|13.20|
    t2     |11.20| 8.10|     |
    t6     |     |20.10|     |

### 三、sql写法 ###

select
    
    factory as "厂区",
    sum(case when energy_type =0 then value end)as "电",
    sum(case when energy_type =1 then value end)as "水",
    sum(case when energy_type =2 then value end)as "气"
    
    from energy_consume ec group by factory

## group by 搭配 max(case when..then..end) as field ##

还有一种需求，是使用max()。格式形如：

select max(case when..then..end) as field1 from t1 group by xxx

例如：根据状态来取值，有状态1，状态2，状态3，通过group by 已经将某张单据的明细数据做了聚合，现在需要判断，如果状态字段有状态1，则取状态1的值，如果没有，则取状态2的值，以此类推。

select
    
    business_no as "单据号",
    max(case when status = 0 then value end)as "状态1的值",
    max(case when status = 1 then value end)as "状态2的值",
    max(case when status =2 then value end)as "状态3的值"
    
    from ali_order group by business_no

这样就把同一单据号下多行数据(状态1的数据，状态2的数据..)变成了一行，接着用if，或者coalesce函数取值即可。

以下依然以能源数据作为案例。

### 一、准备工作 ###

#### 1.建表 ####

CREATE TABLE `energy_data` (
      `id` varchar(64) NOT NULL ,
      `energy_type` varchar(64) NOT NULL COMMENT '能源类型',
      `factory` varchar(64) NOT NULL COMMENT '厂区',
      `start_time` datetime DEFAULT NULL COMMENT '开始时间',
      `end_time` datetime DEFAULT NULL COMMENT '结束时间',
      `value` decimal(20,5) DEFAULT NULL COMMENT '消耗值',
      `input_type` varchar(20) DEFAULT NULL COMMENT '落库方式',
      `update_date` datetime DEFAULT NULL COMMENT '修改时间'
    )comment '能源数据'

#### 2.数据 ####

INSERT INTO energy_data(id,energy_type, factory,  start_time, end_time, value, input_type, update_date)VALUES
    ('1673669924273856513','1', 't1', '2023-06-01 00:00:00', '2023-06-01 00:15:00', 15.23152, 'input',  '2023-06-01 01:15:02'),
    ('1673669924319993858','1', 't1', '2023-06-02 00:15:00', '2023-06-02 00:30:00', 16.23152, 'input',  '2023-06-01 01:15:02'),
    ('1673669924319993859','1', 't1', '2023-06-02 00:30:00', '2023-06-02 00:45:00', 17.23152, 'auto',  '2023-06-01 00:15:02'),
    ('1673669924319993860','1', 't2', '2023-06-01 00:00:00', '2023-06-01 00:15:00', 18.23152, 'input',  '2023-06-01 01:15:02'),
    ('1673669924319993861','1', 't2', '2023-06-02 00:15:00', '2023-06-02 00:30:00', 19.23152, 'input',  '2023-06-01 01:15:02'),
    ('1673669924319993862','1', 't2', '2023-06-02 00:00:00', '2023-06-02 00:15:00', 20.23152, 'auto',  '2023-06-01 01:15:02'),
    ('1673669924319993863','1', 't3', '2023-06-01 00:00:00', '2023-06-01 00:15:00', 21.23152, 'auto',  '2023-06-01 01:15:02'),
    ('1673669924319993864','1', 't3', '2023-06-01 00:15:00', '2023-06-01 00:30:00', 22.23152, 'auto',  '2023-06-01 01:15:02'),
    ('1673669924319993865','1', 't3', '2023-06-01 00:00:00', '2023-06-01 00:15:00', 15.23152, 'auto',  '2023-06-01 01:15:02');

#### 3.查询结果 ####

id                 |energy_type|factory|start_time         |end_time           |value   |input_type|update_date        |
    -------------------+-----------+-------+-------------------+-------------------+--------+----------+-------------------+
    1673233887430275074|1          |t1     |2023-06-01 00:00:00|2023-06-01 00:15:00|15.23152|input     |2023-06-01 01:15:02|
    1673231853909508097|1          |t1     |2023-06-02 00:15:00|2023-06-02 00:30:00|16.23152|input     |2023-06-01 01:15:02|
    1673231709741252610|1          |t1     |2023-06-02 00:30:00|2023-06-02 00:45:00|17.23152|auto      |2023-06-01 00:15:02|
    1673231384053547010|1          |t2     |2023-06-01 00:00:00|2023-06-01 00:15:00|18.23152|input     |2023-06-01 01:15:02|
    1673231046462406658|1          |t2     |2023-06-02 00:15:00|2023-06-02 00:30:00|19.23152|input     |2023-06-01 01:15:02|
    1673230437153308674|1          |t2     |2023-06-02 00:00:00|2023-06-02 00:15:00|20.23152|auto      |2023-06-01 01:15:02|
    1673230091932729345|1          |t3     |2023-06-01 00:00:00|2023-06-01 00:15:00|21.23152|auto      |2023-06-01 01:15:02|
    1673228856448516098|1          |t3     |2023-06-01 00:15:00|2023-06-01 00:30:00|22.23152|auto      |2023-06-01 01:15:02|
    1673224568747393025|1          |t3     |2023-06-01 00:00:00|2023-06-01 00:15:00|15.23152|auto      |2023-06-01 01:15:02|

### 二、需求 ###

目前有各厂区每天的能源数据，数据来源为定时任务从大数据的数据库里每5分钟取一次，input\_type为auto。有时因系统出现问题，数据丢失，会人工手录，input\_type为input。

现在想将每个厂区按照每天来统计当日总量，input\_type是同一类型的相加，即auto的加一起，input的加一起。某一个厂区在一天内，如果input\_type既有input和auto的数据，则使用input的数据，如果只有auto的数据，则使用auto的数据。

### 三、sql写法 ###

#### 1.0版本 ####

先计算各厂区每日的总量。input的加一起，auto的加一起。

这时，各厂区每日的数据因为input\_type的缘故，为多行数据。

select factory,DATE_FORMAT(start_time,'%Y-%m-%d')as start_time,input_type,sum(value)as value
    from energy_data t1 group by factory,DATE_FORMAT(start_time,'%Y-%m-%d'),input_type

factory|start_time|input_type|value   |
    -------+----------+----------+--------+
    t1     |2023-06-01|input     |15.23152|
    t1     |2023-06-02|auto      |17.23152|
    t1     |2023-06-02|input     |16.23152|
    t2     |2023-06-01|input     |18.23152|
    t2     |2023-06-02|auto      |20.23152|
    t2     |2023-06-02|input     |19.23152|
    t3     |2023-06-01|auto      |58.69456|

#### 2.0版本 ####

在1.0的基础上，使用 **group by** 搭配 **max(case when)** 来将**行转列。**

行转列的目的是**将各厂区每日的数据合并为一行**。

select
    factory,start_time,
    max(case when input_type='input' then value end)as input_value,
    max(case when input_type='auto' then value end)as auto_value
    from(
    select factory,DATE_FORMAT(start_time,'%Y-%m-%d')as start_time,input_type,sum(value)as value
    from energy_data t1 group by factory,DATE_FORMAT(start_time,'%Y-%m-%d'),input_type) t2
    group by factory,start_time

factory|start_time|input_value|auto_value|
    -------+----------+-----------+----------+
    t1     |2023-06-01|   15.23152|          |
    t1     |2023-06-02|   16.23152|  17.23152|
    t2     |2023-06-01|   18.23152|          |
    t2     |2023-06-02|   19.23152|  20.23152|
    t3     |2023-06-01|           |  58.69456|

#### 3.0版本 ####

在2.0基础上，使用coalesce()函数。

由于每日的数据已经成为一行，input和auto已经转成字段，我们只需要挑选需要的那个字段的值即可。

select
    factory,start_time,
    COALESCE(max(case when input_type='input' then value end),max(case when input_type='auto' then value end))as value
    from(
    select factory,DATE_FORMAT(start_time,'%Y-%m-%d')as start_time,input_type,sum(value)as value
    from energy_data t1 group by factory,DATE_FORMAT(start_time,'%Y-%m-%d'),input_type) t2
    group by factory,start_time

factory|start_time|value   |
    -------+----------+--------+
    t1     |2023-06-01|15.23152|
    t1     |2023-06-02|16.23152|
    t2     |2023-06-01|18.23152|
    t2     |2023-06-02|19.23152|
    t3     |2023-06-01|58.69456|

参考

[SQL之CASE WHEN用法详解][SQL_CASE WHEN]

[Mysql 纵表转换为横表][Mysql]

=======================分界线=========================

以下为紫薯布丁

CREATE TABLE \`energy\_data\` (  
\`id\` varchar(64) NOT NULL ,  
\`energy\_type\` varchar(64) NOT NULL COMMENT '能源类型',  
\`factory\` varchar(64) NOT NULL COMMENT '厂区',  
\`start\_time\` datetime DEFAULT NULL COMMENT '开始时间',  
\`end\_time\` datetime DEFAULT NULL COMMENT '结束时间',  
\`value\` decimal(20,5) DEFAULT NULL COMMENT '消耗值',  
\`input\_type\` varchar(20) DEFAULT NULL COMMENT '落库方式',  
\`update\_date\` datetime DEFAULT NULL COMMENT '修改时间'  
)comment '能源数据'

INSERT INTO energy\_data(id,energy\_type, factory, start\_time, end\_time, value, input\_type, update\_date)VALUES  
('1673669924273856513','1', 't1', '2023-06-01 00:00:00', '2023-06-01 00:15:00', 15.23152, 'input', '2023-06-01 01:15:02'),  
('1673669924319993858','1', 't1', '2023-06-02 00:15:00', '2023-06-02 00:30:00', 16.23152, 'input', '2023-06-01 01:15:02'),  
('1673669924319993859','1', 't1', '2023-06-02 00:30:00', '2023-06-02 00:45:00', 17.23152, 'auto', '2023-06-01 00:15:02'),  
('1673669924319993860','1', 't2', '2023-06-01 00:00:00', '2023-06-01 00:15:00', 18.23152, 'input', '2023-06-01 01:15:02'),  
('1673669924319993861','1', 't2', '2023-06-02 00:15:00', '2023-06-02 00:30:00', 19.23152, 'input', '2023-06-01 01:15:02'),  
('1673669924319993862','1', 't2', '2023-06-02 00:00:00', '2023-06-02 00:15:00', 20.23152, 'auto', '2023-06-01 01:15:02'),  
('1673669924319993863','1', 't3', '2023-06-01 00:00:00', '2023-06-01 00:15:00', 21.23152, 'auto', '2023-06-01 01:15:02'),  
('1673669924319993864','1', 't3', '2023-06-01 00:15:00', '2023-06-01 00:30:00', 22.23152, 'auto', '2023-06-01 01:15:02'),  
('1673669924319993865','1', 't3', '2023-06-01 00:00:00', '2023-06-01 00:15:00', 15.23152, 'auto', '2023-06-01 01:15:02');

id |energy\_type|factory|start\_time |end\_time |value |input\_type|update\_date |  
\-------------------+-----------+-------+-------------------+-------------------+--------+----------+-------------------+  
1673233887430275074|1 |t1 |2023-06-01 00:00:00|2023-06-01 00:15:00|15.23152|input |2023-06-01 01:15:02|  
1673231853909508097|1 |t1 |2023-06-02 00:15:00|2023-06-02 00:30:00|16.23152|input |2023-06-01 01:15:02|  
1673231709741252610|1 |t1 |2023-06-02 00:30:00|2023-06-02 00:45:00|17.23152|auto |2023-06-01 00:15:02|  
1673231384053547010|1 |t2 |2023-06-01 00:00:00|2023-06-01 00:15:00|18.23152|input |2023-06-01 01:15:02|  
1673231046462406658|1 |t2 |2023-06-02 00:15:00|2023-06-02 00:30:00|19.23152|input |2023-06-01 01:15:02|  
1673230437153308674|1 |t2 |2023-06-02 00:00:00|2023-06-02 00:15:00|20.23152|auto |2023-06-01 01:15:02|  
1673230091932729345|1 |t3 |2023-06-01 00:00:00|2023-06-01 00:15:00|21.23152|auto |2023-06-01 01:15:02|  
1673228856448516098|1 |t3 |2023-06-01 00:15:00|2023-06-01 00:30:00|22.23152|auto |2023-06-01 01:15:02|  
1673224568747393025|1 |t3 |2023-06-01 00:00:00|2023-06-01 00:15:00|15.23152|auto |2023-06-01 01:15:02|

select factory,DATE\_FORMAT(start\_time,'%Y-%m-%d')as start\_time,input\_type,sum(value)as value  
from energy\_data t1 group by factory,DATE\_FORMAT(start\_time,'%Y-%m-%d'),input\_type

factory|start\_time|input\_type|value |  
\-------+----------+----------+--------+  
t1 |2023-06-01|input |15.23152|  
t1 |2023-06-02|auto |17.23152|  
t1 |2023-06-02|input |16.23152|  
t2 |2023-06-01|input |18.23152|  
t2 |2023-06-02|auto |20.23152|  
t2 |2023-06-02|input |19.23152|  
t3 |2023-06-01|auto |58.69456|

select  
factory,start\_time,  
max(case when input\_type='input' then value end)as input\_value,  
max(case when input\_type='auto' then value end)as auto\_value  
from(  
select factory,DATE\_FORMAT(start\_time,'%Y-%m-%d')as start\_time,input\_type,sum(value)as value  
from energy\_data t1 group by factory,DATE\_FORMAT(start\_time,'%Y-%m-%d'),input\_type) t2  
group by factory,start\_time

factory|start\_time|input\_value|auto\_value|  
\-------+----------+-----------+----------+  
t1 |2023-06-01| 15.23152| |  
t1 |2023-06-02| 16.23152| 17.23152|  
t2 |2023-06-01| 18.23152| |  
t2 |2023-06-02| 19.23152| 20.23152|  
t3 |2023-06-01| | 58.69456|

select  
factory,start\_time,  
COALESCE(max(case when input\_type='input' then value end),max(case when input\_type='auto' then value end))as value  
from(  
select factory,DATE\_FORMAT(start\_time,'%Y-%m-%d')as start\_time,input\_type,sum(value)as value  
from energy\_data t1 group by factory,DATE\_FORMAT(start\_time,'%Y-%m-%d'),input\_type) t2  
group by factory,start\_time

factory|start\_time|value |  
\-------+----------+--------+  
t1 |2023-06-01|15.23152|  
t1 |2023-06-02|16.23152|  
t2 |2023-06-01|18.23152|  
t2 |2023-06-02|19.23152|  
t3 |2023-06-01|58.69456|

[SQL_CASE WHEN]: https://blog.csdn.net/rongtaoup/article/details/82183743
[Mysql]: https://blog.csdn.net/qq_19734597/article/details/119325030