2021-05-20

重新格式化部门表

需求一：编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。

展示效果：

id	Jan_Revenue	Feb_Revenue	Mar_Revenue	…	Dec_Revenue
1	8000	7000	6000	…	null
2	9000	null	null	…	null
3	null	10000	null	…	null

Create table If Not Exists 57_Department (id int, revenue int, month varchar(5));
Truncate table 57_Department;
insert into 57_Department (id, revenue, month) values (1, 8000, 'Jan');
insert into 57_Department (id, revenue, month) values (2, 9000, 'Jan');
insert into 57_Department (id, revenue, month) values (3, 10000, 'Feb');
insert into 57_Department (id, revenue, month) values (1, 7000, 'Feb');
insert into 57_Department (id, revenue, month) values (1, 6000, 'Mar');

最终SQL:

SELECT 
      DISTINCT id AS "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_Revenue" 
FROM 
      57_Department
GROUP BY id;