LeetCode-SQL-1179.-重新格式化部门表（GROUP BY）

z754916067

于 2022-04-20 11:54:20 发布

阅读量111

点赞数

分类专栏： LeetCode 数据仓库文章标签： sql

本文链接：https://blog.csdn.net/z754916067/article/details/124293667

版权

LeetCode 同时被 2 个专栏收录

212 篇文章 0 订阅

订阅专栏

数据仓库

41 篇文章 3 订阅

订阅专栏

博客探讨了如何优化SQL查询，从原本使用12个子查询的方法导致在测试中失败，转向使用聚合函数如SUM()结合IF条件来解决问题。通过案例分析，解释了在聚合后的数据上使用CASE WHEN或IF函数的正确方式，以避免重复计算并确保每个月份的收入正确汇总。

摘要由CSDN通过智能技术生成

题目

在这里插入图片描述

思路

自己写了一版12个子查询…果不其然在第十个测试用例就炸了。

SELECT
    uid as id,
    Jan_Revenue,
    Feb_Revenue,
    Mar_Revenue,
    Apr_Revenue,
    May_Revenue,
    Jun_Revenue,
    Jul_Revenue,
    Aug_Revenue,
    Sep_Revenue,
    Oct_Revenue,
    Nov_Revenue,
    Dec_Revenue
FROM
(
SELECT
    distinct id as uid,
    IF((SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Jan") is not null,(SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Jan"),null) as Jan_Revenue,
    IF((SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Feb") is not null,(SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Feb"),null) as Feb_Revenue,
    IF((SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Mar") is not null,(SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Mar"),null) as Mar_Revenue,
    IF((SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Apr") is not null,(SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Apr"),null) as Apr_Revenue,
    IF((SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="May") is not null,(SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="May"),null) as May_Revenue,
    IF((SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Jun") is not null,(SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Jun"),null) as Jun_Revenue,
    IF((SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Jul") is not null,(SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Jul"),null) as Jul_Revenue,
    IF((SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Aug") is not null,(SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Aug"),null) as Aug_Revenue,
    IF((SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Sep") is not null,(SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Sep"),null) as Sep_Revenue,
    IF((SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Oct") is not null,(SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Oct"),null) as Oct_Revenue,
    IF((SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Nov") is not null,(SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Nov"),null) as Nov_Revenue,
    IF((SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Dec") is not null,(SELECT revenue FROM Department as D1 WHERE D1.id=uid AND month="Dec"),null) as Dec_Revenue
FROM
    Department
) as t1

润去题解，首先group id，然后根据

https://blog.csdn.net/u014717572/article/details/80687042

里的解析来看，假设出现了一张虚拟表
在这里插入图片描述

此时想要取出id=1且month="Jan"里的revenue 可以用

 CASE WHEN month='Jan' THEN revenue END 
 或者
 IF(Month='Jan',revenue,null)

上面说的并不对，因为对于已经聚合的行，2中的虚拟表里该id有多少行，就会执行该语句多少次。
所以变成如下情况

这样只会打印出第一行，当然月份为Jan时是对的，但当月份为Feb时，打印出来的就是null了。
5. 故需要一个聚合函数，max()，sum()都可以实现

代码

SELECT 
id, 
SUM(IF(Month='Jan',revenue,null)) AS Jan_Revenue,
SUM(IF(Month='Feb',revenue,null)) AS Feb_Revenue,
SUM(IF(Month='Mar',revenue,null)) AS Mar_Revenue,
SUM(IF(Month='Apr',revenue,null)) AS Apr_Revenue,
SUM(IF(Month='May',revenue,null)) AS May_Revenue,
SUM(IF(Month='Jun',revenue,null)) AS Jun_Revenue,
SUM(IF(Month='Jul',revenue,null)) AS Jul_Revenue,
SUM(IF(Month='Aug',revenue,null)) AS Aug_Revenue,
SUM(IF(Month='Sep',revenue,null)) AS Sep_Revenue,
SUM(IF(Month='Oct',revenue,null)) AS Oct_Revenue,
SUM(IF(Month='Nov',revenue,null)) AS Nov_Revenue,
SUM(IF(Month='Dec',revenue,null)) AS Dec_Revenue
FROM department
GROUP BY id
ORDER BY id;