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

zzamx

已于 2023-03-21 13:24:41 修改

阅读量151

点赞数

于 2023-03-21 12:41:07 首次发布

本文链接：https://blog.csdn.net/xiaoyue_/article/details/129685824

版权

Leetcode 同时被 2 个专栏收录

119 篇文章 2 订阅

订阅专栏

SQL

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