本文共 2342 字,大约阅读时间需要 7 分钟。
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 著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。需要将revenue列根据month拆成行,有两种方式,CASE WHEN或者IF语句。
根据题意,需要根据id,将相同id的行进行合并,结果如下表,采用分组的方式,可以对各月收入列求SUM/MAX/MIN/AVG都可以。
select id,sum(case month when 'Jan' then revenue else null end) as Jan_Revenue,sum(case month when 'Feb' then revenue else null end) as Feb_Revenue,sum(case month when 'Mar' then revenue else null end) as Mar_Revenue,sum(case month when 'Apr' then revenue else null end) as Apr_Revenue,sum(case month when 'May' then revenue else null end) as May_Revenue,sum(case month when 'Jun' then revenue else null end) as Jun_Revenue,sum(case month when 'Jul' then revenue else null end) as Jul_Revenue,sum(case month when 'Aug' then revenue else null end) as Aug_Revenue,sum(case month when 'Sep' then revenue else null end) as Sep_Revenue,sum(case month when 'Oct' then revenue else null end) as Oct_Revenue,sum(case month when 'Nov' then revenue else null end) as Nov_Revenue,sum(case month when 'Dec' then revenue else null end) as Dec_Revenuefrom departmentgroup by id
select distinct 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_Revenuefrom Department group by id order by id;