7 重新格式化部门表

[astak16]

题目

重新格式化表，是的新的表中有一个 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 等