sql 行转列 case when (简洁版) —— group by 行与行比较【mysql】【全网最全】
mysql 行转列 case when (简洁版) —— group by 行与行比较
目录
group by 搭配 max(case when..then..end) as field
之前写了一版使用 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 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
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|
开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！
更多推荐
1
0- 0
已为社区贡献14条内容
所有评论(0)