mysql 行转列 case when

globalcoding

已于 2023-06-29 22:48:32 修改

阅读量1.2k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql 行转列 group case max

于 2023-06-27 18:38:03 首次发布

本文链接：https://blog.csdn.net/u011149152/article/details/131416334

本文介绍了如何利用SQL中的groupby与casewhen语句进行数据汇总，特别是在能源消耗数据上，分别展示了如何通过sum和max函数将不同类型的能源消耗转换为列并按工厂分组，以及如何处理存在多种输入类型情况下的数据聚合，确保优先选取特定输入类型的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

省流

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

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

省流

使用 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 COMMEN