LeetCode数据库1179

编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。

查询结果格式如下面的示例所示：

讲讲case when的原理
当一个单元格中有多个数据时，case when只会提取当中的第一个数据。

以CASE WHEN month=‘Feb’ THEN revenue END 为例，当id=1时，它只会提取month对应单元格里的第一个数据，即Jan，它不等于Feb，所以找不到Feb对应的revenue，所以返回NULL。（可以试试把我上面答案里的sum()统统去掉，执行结果与预期不一样。错就错在当id=1时,Feb_Revenue和Mar_Revenue的值变成了NULL）

那该如何解决单元格内含多个数据的情况呢？答案就是使用聚合函数，聚合函数就用来输入多个数据，输出一个数据的。如SUM()或MAX()，而每个聚合函数的输入就是每一个多数据的单元格。

以SUM(CASE WHEN month=‘Feb’ THEN revenue END) 为例，当id=1时，它提取的Jan、Feb、Mar，从中找到了符合条件的Feb，并最终返回对应的revenue的值，即7000。

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

SELECT id,
sum(IF(`month`='Jan',revenue,NULL)) Jan_Revenue,
sum(IF(`month`='Feb',revenue,NULL)) Feb_Revenue,
sum(IF(`month`='Mar',revenue,NULL)) Mar_Revenue,
sum(IF(`month`='Apr',revenue,NULL)) Apr_Revenue,
sum(IF(`month`='May',revenue,NULL)) May_Revenue,
sum(IF(`month`='Jun',revenue,NULL)) Jun_Revenue,
sum(IF(`month`='Jul',revenue,NULL)) Jul_Revenue,
sum(IF(`month`='Aug',revenue,NULL)) Aug_Revenue,
sum(IF(`month`='Sep',revenue,NULL)) Sep_Revenue,
sum(IF(`month`='Oct',revenue,NULL)) Oct_Revenue,
sum(IF(`month`='Nov',revenue,NULL)) Nov_Revenue,
sum(IF(`month`='Dec',revenue,NULL)) Dec_Revenue
FROM Department
group by id
order by id