【SQL】1179. 重新格式化部门表(图解 + 为什么使用聚合函数SUM,MAX + 行转列)

64 篇文章 1 订阅

在这里插入图片描述
在这里插入图片描述

写法一: 聚合函数SUM()

select id,
    SUM(CASE WHEN month='Jan' THEN revenue END) as Jan_Revenue,
    SUM(CASE WHEN month='Feb' THEN revenue END) as Feb_Revenue,
    SUM(CASE WHEN month='Mar' THEN revenue END) as Mar_Revenue,
    SUM(CASE WHEN month='Apr' THEN revenue END) as Apr_Revenue,
    SUM(CASE WHEN month='May' THEN revenue END) as May_Revenue,
    SUM(CASE WHEN month='Jun' THEN revenue END) as Jun_Revenue,
    SUM(CASE WHEN month='Jul' THEN revenue END) as Jul_Revenue,
    SUM(CASE WHEN month='Aug' THEN revenue END) as Aug_Revenue,
    SUM(CASE WHEN month='Sep' THEN revenue END) as Sep_Revenue,
    SUM(CASE WHEN month='Oct' THEN revenue END) as Oct_Revenue,
    SUM(CASE WHEN month='Nov' THEN revenue END) as Nov_Revenue,
    SUM(CASE WHEN month='Dec' THEN revenue END) as Dec_Revenue
from Department
group by id
order by id;

输出如下:

在这里插入图片描述

写法二: 聚合函数max()

# Write your MySQL query statement below

select id,
    MAX(CASE WHEN month='Jan' THEN revenue END) as Jan_Revenue,
    MAX(CASE WHEN month='Feb' THEN revenue END) as Feb_Revenue,
    MAX(CASE WHEN month='Mar' THEN revenue END) as Mar_Revenue,
    MAX(CASE WHEN month='Apr' THEN revenue END) as Apr_Revenue,
    MAX(CASE WHEN month='May' THEN revenue END) as May_Revenue,
    MAX(CASE WHEN month='Jun' THEN revenue END) as Jun_Revenue,
    MAX(CASE WHEN month='Jul' THEN revenue END) as Jul_Revenue,
    MAX(CASE WHEN month='Aug' THEN revenue END) as Aug_Revenue,
    MAX(CASE WHEN month='Sep' THEN revenue END) as Sep_Revenue,
    MAX(CASE WHEN month='Oct' THEN revenue END) as Oct_Revenue,
    MAX(CASE WHEN month='Nov' THEN revenue END) as Nov_Revenue,
    MAX(CASE WHEN month='Dec' THEN revenue END) as Dec_Revenue
from Department
group by id
order by id;

在这里插入图片描述

为什么使用聚合函数

  • CASE语句遍历条件并在满足第一个条件时返回一个值(如IF-THEN-ELSE语句)。因此,一旦条件为真,它将停止读取并返回结果。如果没有条件为 true,则返回 ELSE 子句中的值。如果没有其他部分,并且没有条件为 true,则返回 NULL。
  • 对于同一个单元格内多个数据的情况,可以使用聚合函数(多个输入,一个输出)


如果不用SUM() 则,

当 id=1,month='Feb'时,revenue=null;
当 id=1,month='Mar'时,revenue=null;

在这里插入图片描述

总结 :行转列 ==> 利用分组GROUP BY + 聚合函数SUM(), MAX()等

参考资料:
https://leetcode.cn/problems/reformat-department-table/solutions/343480/guan-yu-group-byyu-sumde-pei-he-by-xxiao053/
https://blog.csdn.net/u014717572/article/details/80687042

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值