题目描述
部门表 Department:
Column Name | Type |
---|---|
id | int |
revenue | int |
month | varchar |
(id, month)
是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份(month
)可以取下列值
["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
。
编写一个 SQL
查询来重新格式化表,使得新的表中有一个部门 id
列和一些对应 每个月 的收入(revenue
)列。
查询结果格式如下面的示例所示:
Department
表:
id | revenue | month |
---|---|---|
1 | 8000 | Jan |
2 | 9000 | Jan |
3 | 10000 | Feb |
1 | 7000 | Feb |
1 | 6000 | Mar |
查询得到的结果表:
id | Jan_Revenue | Feb_Revenue | Mar_Revenue | … | Dec_Revenue |
---|---|---|---|---|---|
1 | 8000 | 7000 | 6000 | … | null |
2 | 9000 | null | null | … | null |
3 | null | 10000 | null | … | null |
注意,结果表有 13
列 (1
个部门 id
列 + 12
个月份的收入列)。
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/reformat-department-table
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
解决思路
- 先使用分组查询对
Id
进行分组. - 分组完毕当中的
select
语句当中, 对未参与分组的字段revenue
使用聚合函数. 为了从多个revenue
中选择正确月份的 , 因此使用case when
或if
.
实现代码1 : case when
SELECT id,
SUM(CASE WHEN `month` = 'Jan' THEN revenue END) Jan_Revenue,
SUM(CASE WHEN `month` = 'Feb' THEN revenue END) Feb_Revenue,
SUM(CASE WHEN `month` = 'Mar' THEN revenue END) Mar_Revenue,
SUM(CASE WHEN `month` = 'Apr' THEN revenue END) Apr_Revenue,
SUM(CASE WHEN `month` = 'May' THEN revenue END) May_Revenue,
SUM(CASE WHEN `month` = 'Jun' THEN revenue END) Jun_Revenue,
SUM(CASE WHEN `month` = 'Jul' THEN revenue END) Jul_Revenue,
SUM(CASE WHEN `month` = 'Aug' THEN revenue END) Aug_Revenue,
SUM(CASE WHEN `month` = 'Sep' THEN revenue END) Sep_Revenue,
SUM(CASE WHEN `month` = 'Oct' THEN revenue END) Oct_Revenue,
SUM(CASE WHEN `month` = 'Nov' THEN revenue END) Nov_Revenue,
SUM(CASE WHEN `month` = 'Dec' THEN revenue END) Dec_Revenue
FROM Department GROUP BY id;
实现代码2 : if
SELECT
id,
max(if(month = 'Jan',revenue,null)) Jan_Revenue,
max(if(month = 'Feb',revenue,null)) Feb_Revenue,
max(if(month = 'Mar',revenue,null)) Mar_Revenue,
max(if(month = 'Apr',revenue,null)) Apr_Revenue,
max(if(month = 'May',revenue,null)) May_Revenue,
max(if(month = 'Jun',revenue,null)) Jun_Revenue,
max(if(month = 'Jul',revenue,null)) Jul_Revenue,
max(if(month = 'Aug',revenue,null)) Aug_Revenue,
max(if(month = 'Sep',revenue,null)) Sep_Revenue,
max(if(month = 'Oct',revenue,null)) Oct_Revenue,
max(if(month = 'Nov',revenue,null)) Nov_Revenue,
max(if(month = 'Dec',revenue,null)) Dec_Revenue
FROM Department GROUP BY id