我的编程空间，编程开发者的网络收藏夹
学习永远不晚
位置：首页-资讯-数据库

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

2023-09-26 05:16

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号
获取短信验证
立即预约

请填写图片验证码后获取短信验证码

看不清楚，换张图片

免费获取短信验证码

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

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

行转列sqlgroup比较casewhen
阅读原文内容投诉

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

下载Word文档到电脑，方便收藏和打印～

下载Word文档

相关文章

猜你喜欢

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

2023-09-26

热门标签

Linux(148)PHP(127)Java(102)正则表达式(101)JavaScript(69)最佳实践(67)jQuery(44)MySQL(39)Docker(37)C语言(36)性能优化(34)Python(34)XML(28)string(27)第三方库(23)回调函数(23)ZIP(22)数组(22)可扩展性(22)字符串比较(21)find(20)RPM(20)Go(20)grep(19)ASP.NETCore(19)XML解析器(19)事件(19)事件处理程序(19)StringBuilder(18)Nginx(18)

编程热搜

查看更多

编程资源站

  • 资料下载
  • 历年试题
2021年下半年软考高级信息系统项目管理师高频考点精选资料
2021下半年软考高级信息系统技术知识点记忆口诀精选资料
2021下半年软考《信息系统项目管理师》考试真题及答案精选资料
2021下半年软考高级考试备考攻略精选资料
2021年软考高级《信息系统项目管理师》巩固练习题汇总精选资料
2021下半年软考高级信息系统项目管理师30个易考知识点汇总精选资料
2021下半年软考高级知识点这样记，还担心记不住吗精选资料
2021年下半年软考高级考试重点汇总精选资料
2021下半年软考高级信息系统项目管理师计算公式汇总精选资料
2021年下半年软考高级《信息系统项目管理师》模拟试题精选资料
查看更多
信息系统项目管理师选择题每日一练（2024）历年试题
2023年下半年信息系统项目管理师综合知识真题演练历年试题
查看更多

目录

本网页已闲置超过3分钟，按键盘任意键或点击空白处，即可回到网页
最新资讯更多
    友情链接编程网

    反馈

    我要
    反馈