7 重新格式化部门表
Opened this issue · 0 comments
astak16 commented
题目
重新格式化表,是的新的表中有一个 id
列,和对应每个月的收入列
month
取值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
create table department (
id int,
revenue int,
month varchar(255)
);
insert into department values
(1, 8000, 'Jan'),
(2, 9000, 'Jan'),
(3, 10000, 'Feb'),
(1, 7000, 'Feb'),
(1, 6000, 'Mar');
SQL
select id,
sum(case month when 'Jan' then revenue end) as 'Jan_Revenue',
sum(case month when 'Feb' then revenue end) as 'Feb_Revenue',
sum(case month when 'Mar' then revenue end) as 'Mar_Revenue',
sum(case month when 'Apr' then revenue end) as 'Apr_Revenue',
sum(case month when 'May' then revenue end) as 'May_Revenue',
sum(case month when 'Jun' then revenue end) as 'Jun_Revenue',
sum(case month when 'Jul' then revenue end) as 'Jul_Revenue',
sum(case month when 'Aug' then revenue end) as 'Aug_Revenue',
sum(case month when 'Sep' then revenue end) as 'Sep_Revenue',
sum(case month when 'Oct' then revenue end) as 'Oct_Revenue',
sum(case month when 'Nov' then revenue end) as 'Nov_Revenue',
sum(case month when 'Dec' then revenue end) as 'Dec_Revenue'
from department group by id
解析
department
表中存储这所有人所有月的收入,这里的需求是将 department
的 month
列拆成具体的月份。具体实现:
- 将
department
按照id
进行分组 - 使用
case month when 'Jan' then revenue end
计算出一月份的收入- 也可以使用
if(month = 'Jan', revenue, null)
- 也可以使用
- 每个以此类推,直到 12 个月都计算完
- 因为使用
group by
需要使用聚合函数,这里的聚合函数可以用max
、min
、sum
等