sql 行转列 case when (简洁版) —— group by 行与行比较【mysql】【全网最全】

客官°小女子只卖身不卖艺 2024-03-17 12:14 3阅读 0赞

**目录**

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

一、需求

二、准备工作

1.建表

2.数据

3.查询结果

三、sql写法

1.0版本

2.0版本

3.0版本

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

之前写了一版使用 group by 搭配 case when 来实现行转列，但过于复杂了，今天写一版简洁的

[mysql 行转列 case when\_globalcoding的博客-CSDN博客][mysql _ case when_globalcoding_-CSDN]

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

格式形如：

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

### 一、需求 ###

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

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

以下就以厂区t1来作为案例，有6天的数据。为了简化，每天只筛选部分数据。（原数据每个厂区每个测点每天有288条数据。）

### 二、准备工作 ###

#### 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-05 01:15:02'),
    ('1673669924319993858','1', 't1', '2023-06-02 00:15:00', '2023-06-02 00:30:00', 16.23152, 'input',  '2023-06-05 01:15:02'),
    ('1673669924319993859','1', 't1', '2023-06-02 00:30:00', '2023-06-02 00:45:00', 17.23152, 'auto',  '2023-06-05 00:15:02'),
    ('1673669924319993860','1', 't1', '2023-06-03 00:00:00', '2023-06-03 00:15:00', 18.23152, 'auto',  '2023-06-05 01:15:02'),
    ('1673669924319993861','1', 't1', '2023-06-03 00:15:00', '2023-06-03 00:30:00', 19.23152, 'input',  '2023-06-05 01:15:02'),
    ('1673669924319993862','1', 't1', '2023-06-04 00:00:00', '2023-06-04 00:15:00', 20.23152, 'auto',  '2023-06-05 01:15:02'),
    ('1673669924319993863','1', 't1', '2023-06-05 00:00:00', '2023-06-05 00:15:00', 21.23152, 'auto',  '2023-06-05 01:15:02'),
    ('1673669924319993864','1', 't1', '2023-06-05 00:15:00', '2023-06-05 00:30:00', 22.23152, 'auto',  '2023-06-05 01:15:02'),
    ('1673669924319993865','1', 't1', '2023-06-06 00:00:00', '2023-06-06 00:15:00', 15.23152, 'auto',  '2023-06-05 01:15:02');

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

id                 |energy_type|factory|start_time         |end_time           |value   |input_type|update_date        |
    -------------------+-----------+-------+-------------------+-------------------+--------+----------+-------------------+
    1673669924273856513|1          |t1     |2023-06-01 00:00:00|2023-06-01 00:15:00|15.23152|input     |2023-06-05 01:15:02|
    1673669924319993858|1          |t1     |2023-06-02 00:15:00|2023-06-02 00:30:00|16.23152|input     |2023-06-05 01:15:02|
    1673669924319993859|1          |t1     |2023-06-02 00:30:00|2023-06-02 00:45:00|17.23152|auto      |2023-06-05 00:15:02|
    1673669924319993860|1          |t1     |2023-06-03 00:00:00|2023-06-03 00:15:00|18.23152|auto      |2023-06-05 01:15:02|
    1673669924319993861|1          |t1     |2023-06-03 00:15:00|2023-06-03 00:30:00|19.23152|input     |2023-06-05 01:15:02|
    1673669924319993862|1          |t1     |2023-06-04 00:00:00|2023-06-04 00:15:00|20.23152|auto      |2023-06-05 01:15:02|
    1673669924319993863|1          |t1     |2023-06-05 00:00:00|2023-06-05 00:15:00|21.23152|auto      |2023-06-05 01:15:02|
    1673669924319993864|1          |t1     |2023-06-05 00:15:00|2023-06-05 00:30:00|22.23152|auto      |2023-06-05 01:15:02|
    1673669924319993865|1          |t1     |2023-06-06 00:00:00|2023-06-06 00:15:00|15.23152|auto      |2023-06-05 01:15:02|

###  ###

### 三、sql写法 ###

#### 1.0版本 ####

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

这时，各厂区每日的数据因为input\_type的缘故，有的当日数据会分为auto和input。

sql如下 ：

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

结果为：

factory|start_date|input_type|value   |
    -------+----------+----------+--------+
    t1     |2023-06-01|input     |15.23152|
    t1     |2023-06-02|auto      |17.23152|
    t1     |2023-06-02|input     |16.23152|
    t1     |2023-06-03|auto      |18.23152|
    t1     |2023-06-03|input     |19.23152|
    t1     |2023-06-04|auto      |20.23152|
    t1     |2023-06-05|auto      |43.46304|
    t1     |2023-06-06|auto      |15.23152|

#### 2.0版本 ####

我们现在需要对 input 和 auto 做个筛选。当日数据如果有input的，就使用input的，如果没有input的，就使用auto。

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

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

sql如下：

select
    factory,start_date,
    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_date,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_date

结果为：

factory|start_date|input_value|auto_value|
    -------+----------+-----------+----------+
    t1     |2023-06-01|   15.23152|          |
    t1     |2023-06-02|   16.23152|  17.23152|
    t1     |2023-06-03|   19.23152|  18.23152|
    t1     |2023-06-04|           |  20.23152|
    t1     |2023-06-05|           |  43.46304|
    t1     |2023-06-06|           |  15.23152|

#### 3.0版本 ####

现在只需要将有值的字段，作为 value 返回即可。

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

由于当日数据已经转成一行，input和auto已经转成字段，我们只需要挑选需要的那个字段的值即可。coalesce(f1,f2,f3...)函数刚好满足这个需求，f1如果不为空，就返回f1，f1如果为空，就看f2，f2如果不为空，就返回f2，以此类推。简单一点说就是，f1如果为空，就返回f2，f2如果为空，就返回f3。

sql如下：

select
    factory,start_date,
    COALESCE(max(case when input_type='input' then value end),max(case when input_type='auto' then value end),0) as value
    
    from(
        select factory,DATE_FORMAT(start_time,'%Y-%m-%d')as start_date,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_date

结果为：

factory|start_date|value   |
    -------+----------+--------+
    t1     |2023-06-01|15.23152|
    t1     |2023-06-02|16.23152|
    t1     |2023-06-03|19.23152|
    t1     |2023-06-04|20.23152|
    t1     |2023-06-05|43.46304|
    t1     |2023-06-06|15.23152|

**以上sql为本人亲写亲测，请尽情食用。**

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

以下为紫薯布丁

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-05 01:15:02'),  
('1673669924319993858','1', 't1', '2023-06-02 00:15:00', '2023-06-02 00:30:00', 16.23152, 'input', '2023-06-05 01:15:02'),  
('1673669924319993859','1', 't1', '2023-06-02 00:30:00', '2023-06-02 00:45:00', 17.23152, 'auto', '2023-06-05 00:15:02'),  
('1673669924319993860','1', 't1', '2023-06-03 00:00:00', '2023-06-03 00:15:00', 18.23152, 'auto', '2023-06-05 01:15:02'),  
('1673669924319993861','1', 't1', '2023-06-03 00:15:00', '2023-06-03 00:30:00', 19.23152, 'input', '2023-06-05 01:15:02'),  
('1673669924319993862','1', 't1', '2023-06-04 00:00:00', '2023-06-04 00:15:00', 20.23152, 'auto', '2023-06-05 01:15:02'),  
('1673669924319993863','1', 't1', '2023-06-05 00:00:00', '2023-06-05 00:15:00', 21.23152, 'auto', '2023-06-05 01:15:02'),  
('1673669924319993864','1', 't1', '2023-06-05 00:15:00', '2023-06-05 00:30:00', 22.23152, 'auto', '2023-06-05 01:15:02'),  
('1673669924319993865','1', 't1', '2023-06-06 00:00:00', '2023-06-06 00:15:00', 15.23152, 'auto', '2023-06-05 01:15:02');

id |energy\_type|factory|start\_time |end\_time |value |input\_type|update\_date |  
\-------------------+-----------+-------+-------------------+-------------------+--------+----------+-------------------+  
1673669924273856513|1 |t1 |2023-06-01 00:00:00|2023-06-01 00:15:00|15.23152|input |2023-06-05 01:15:02|  
1673669924319993858|1 |t1 |2023-06-02 00:15:00|2023-06-02 00:30:00|16.23152|input |2023-06-05 01:15:02|  
1673669924319993859|1 |t1 |2023-06-02 00:30:00|2023-06-02 00:45:00|17.23152|auto |2023-06-05 00:15:02|  
1673669924319993860|1 |t1 |2023-06-03 00:00:00|2023-06-03 00:15:00|18.23152|auto |2023-06-05 01:15:02|  
1673669924319993861|1 |t1 |2023-06-03 00:15:00|2023-06-03 00:30:00|19.23152|input |2023-06-05 01:15:02|  
1673669924319993862|1 |t1 |2023-06-04 00:00:00|2023-06-04 00:15:00|20.23152|auto |2023-06-05 01:15:02|  
1673669924319993863|1 |t1 |2023-06-05 00:00:00|2023-06-05 00:15:00|21.23152|auto |2023-06-05 01:15:02|  
1673669924319993864|1 |t1 |2023-06-05 00:15:00|2023-06-05 00:30:00|22.23152|auto |2023-06-05 01:15:02|  
1673669924319993865|1 |t1 |2023-06-06 00:00:00|2023-06-06 00:15:00|15.23152|auto |2023-06-05 01:15:02|

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

factory|start\_date|input\_type|value |  
\-------+----------+----------+--------+  
t1 |2023-06-01|input |15.23152|  
t1 |2023-06-02|auto |17.23152|  
t1 |2023-06-02|input |16.23152|  
t1 |2023-06-03|auto |18.23152|  
t1 |2023-06-03|input |19.23152|  
t1 |2023-06-04|auto |20.23152|  
t1 |2023-06-05|auto |43.46304|  
t1 |2023-06-06|auto |15.23152|

select  
factory,start\_date,  
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\_date,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\_date

factory|start\_date|input\_value|auto\_value|  
\-------+----------+-----------+----------+  
t1 |2023-06-01| 15.23152| |  
t1 |2023-06-02| 16.23152| 17.23152|  
t1 |2023-06-03| 19.23152| 18.23152|  
t1 |2023-06-04| | 20.23152|  
t1 |2023-06-05| | 43.46304|  
t1 |2023-06-06| | 15.23152|

select  
factory,start\_date,  
COALESCE(max(case when input\_type='input' then value end),max(case when input\_type='auto' then value end),0) as value

factory|start\_date|value |  
\-------+----------+--------+  
t1 |2023-06-01|15.23152|  
t1 |2023-06-02|16.23152|  
t1 |2023-06-03|19.23152|  
t1 |2023-06-04|20.23152|  
t1 |2023-06-05|43.46304|  
t1 |2023-06-06|15.23152|

[mysql _ case when_globalcoding_-CSDN]: https://globalcoding.blog.csdn.net/article/details/131416334