LeetCode 的一道题 忘记了用聚合函数进行操作
题目要求用
月份(month)可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。
编写一个 SQL 查询来重新格式化表,使得新的表中有一个部门 id 列和一些对应 每个月 的收入(revenue)列。
内容为
此处 很容易想到用group by id 进行分组,然后将month进行分成12各月份的列值为revenue
但是如果用join 月份发现不太行,实际上再用聚合函数时就解决了问题,可以直接进行作为一列值。
SELECT d1.id AS 'id',
SUM(CASE d1.month WHEN 'Jan' THEN revenue ELSE NULL END) AS 'Jan_Revenue',
SUM(CASE d1.month WHEN 'Feb' THEN revenue ELSE NULL END) AS 'Feb_Revenue',
SUM(CASE d1.month WHEN 'Mar' THEN revenue ELSE NULL END) AS 'Mar_Revenue',
SUM(CASE d1.month WHEN 'Apr' THEN revenue ELSE NULL END) AS 'Apr_Revenue',
SUM(CASE d1.month WHEN 'May' THEN revenue ELSE NULL END) AS 'May_Revenue',
SUM(CASE d1.month WHEN 'Jun' THEN revenue ELSE NULL END) AS 'Jun_Revenue',
SUM(CASE d1.month WHEN 'Jul' THEN revenue ELSE NULL END) AS 'Jul_Revenue',
SUM(CASE d1.month WHEN 'Aug' THEN revenue ELSE NULL END) AS 'Aug_Revenue',
SUM(CASE d1.month WHEN 'Sep' THEN revenue ELSE NULL END) AS 'Sep_Revenue',
SUM(CASE d1.month WHEN 'Oct' THEN revenue ELSE NULL END) AS 'Oct_Revenue',
SUM(CASE d1.month WHEN 'Nov' THEN revenue ELSE NULL END) AS 'Nov_Revenue',
SUM(CASE d1.month WHEN 'Dec' THEN revenue ELSE NULL END) AS 'Dec_Revenue'
FROM Department AS d1
GROUP BY d1.id