mysql聚合函数参与的行转列 列转行的用法

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