- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
目录
一,原题力扣链接
二,题干
表
Department
:+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | revenue | int | | month | varchar | +---------------+---------+ 在 SQL 中,(id, month) 是表的联合主键。 这个表格有关于每个部门每月收入的信息。 月份(month)可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。重新格式化表格,使得 每个月 都有一个部门 id 列和一个收入列。
以 任意顺序 返回结果表。
结果格式如以下示例所示。
示例 1:
输入: Department table: +------+---------+-------+ | 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 列用于各个月份)。
三,建表语句
Create table If Not Exists Department (id int, revenue int, month varchar(5));
Truncate table Department;
insert into Department (id, revenue, month) values ('1', '8000', 'Jan');
insert into Department (id, revenue, month) values ('2', '9000', 'Jan');
insert into Department (id, revenue, month) values ('3', '10000', 'Feb');
insert into Department (id, revenue, month) values ('1', '7000', 'Feb');
insert into Department (id, revenue, month) values ('1', '6000', 'Mar');
四,分析
思路
mysql
第一步:在mysql中 以id分组 就可以对id去重
第二步:分别依次用if判断,如果月份等于某某,就取对应的列,放置就给null;
第三步:因为是分组,所以前面的列必然要聚合一下 用sum巧妙的聚合
五,SQL解答
select
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 Department group by id;
六,验证
七,知识点总结
- if判断的运用
- 巧妙的用分组+sum 实现透视表的效果
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用