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

目录

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博客

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 valuefrom 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如下： 

selectfactory,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_valuefrom(    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) t2group 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如下：

selectfactory,start_date,COALESCE(max(case when input_type='input' then value end),max(case when input_type='auto' then value end),0) as valuefrom(    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) t2group 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

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|

来源地址：https://blog.csdn.net/u011149152/article/details/131442054