33 查询员工的累计薪水

[astak16]

题目

编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。

结果请按 id 升序，然后按 month 降序显示。

create table employee(
	id int,
	month int,
	salary int
)

insert into employee values
(1, 1, 20),
(2, 1, 20),
(1, 2, 30),
(2, 2, 30),
(3, 2, 40),
(1, 3, 40),
(3, 3, 60),
(1, 4, 60),
(3, 4, 70);

SQL：方法一

select
	e1.id,
	e1.month,
	(ifnull(e1.salary, 0) + ifnull(e2.salary, 0) + ifnull(e3.salary, 0)) salary
from employee e1
left join employee e2 on e2.id = e1.id and e2.month = e1.month - 1
left join employee e3 on e3.id = e1.id and e3.month = e1.month - 2
join (
	select id, max(month) max_month from employee group by id
) e4 on e4.id = e1.id and e4.max_month > e1.month
order by e1.id, e1.month desc;

解析

1. 因为要累计计算最近三个月的薪资，所以就要连续自连三次，自连的条件是 id 和 month - 1 和 month - 2

   select * from employee e1
   left join employee e2 on e2.id = e1.id and e2.month = e1.month - 1
   left join employee e3 on e3.id = e1.id and e3.month = e1.month - 2
   order by e1.id, e1.salary desc;

   这里如果使用 e2.month - 1 = e1.month 和 e3.month - 2 = e1.month 会有问题，因为我们这里最后会计算 e1.salary + e2.salary + e3.salary 。

   这里以 id = 1 举例：

   这是 e2.month = e1.month - 1 和 e3.month = e1.month - 2 的结果

   e1.id	e1.month	e1.salary	e2.id	e2.month	e2.salary	e3.id	e3.month	e3.salary
   1	4	60	1	3	40	1	3	30
   1	3	40	1	2	30	1	2	20
   1	2	30	1	1	20	null	null	null
   1	1	20	null	null	null	null	null	null

   这是 e2.month - 1 = e1.month 和 e3.month - 2 = e1.month 的结果

   e1.id	e1.month	e1.salary	e2.id	e2.month	e2.salary	e3.id	e3.month	e3.salary
   1	4	60	null	null	null	null	null	null
   1	3	40	1	4	60	null	null	null
   1	2	30	1	3	40	1	4	60
   1	1	20	1	2	30	1	3	40

   使用 e2.month - 1 = e1.month 的问题是， e2 表的第二行不是 2 月份的数据，而是 4 月份的数据。

2. 找出每个 id 最大的月份，存储为临时表 e4

   select id, max(month) max_month from employee group by id;

3. 将第一步的结果使用 join 连接 e4，筛选条件 id 和 e1.month < e4.max_month

   select * from employee e1
   left join employee e2 on e2.id = e1.id and e2.month = e1.month - 1
   left join employee e3 on e3.id = e1.id and e3.month = e1.month - 2
   join (
   	select id, max(month) max_month from employee group by id;
   ) e4 on e4.id = e1.id and e4.max_month > e1.month
   order by e1.id, e1.salary desc;

   这里为什么使用 left join 而不是使用 join ，因为要去除掉最大月份，使用 e1.month < e4.max_month 时会有一条数据是 null ，使用 join 能够去除掉这条数据。

4. 使用 ifnull 判断是否每张表的 salary 是否为 null ，并且相加，就是每个月薪资累加的和。

   (ifnull(e1.salary, 0) + ifnull(e2.salary, 0) + ifnull(e3.salary, 0)) salary

Tips

有个问题，如果月份不连续，比如下面的数据 7月 和 4月 不连续，使用这个方法计算 7月 的累计薪资是 7月 、 6月、 5月 ，不会计算到 4月 的薪资。

insert into employee values
(1,1,20),
(1,2,30),
(1,3,40),
(1,4,60),
(1,7,90),
(1,8,90),
(2,1,20),
(2,2,30),
(3,2,40),
(3,3,60),
(3,4,70);

SQL：方法二

select
	id, month,
	sum(salary) over(partition by id
									    order by month desc
										range between 0 preceding and 2 following
	) salary
from employee where (id, month) not in (
 select id, max(month) month from employee group by id
);

select
	id, month,
	sum(salary) over(partition by id
										order by month
										range 2 preceding
	) salary
from employee where (id, month) not in (
	select id, max(month) month from employee group by id
)
order by id, month desc;

select
	id,	month,
	sum(salary) over(partition by id order by month range 2 preceding) salary
from (
	select
		id, month, salary,
		row_number() over(partition by id order by month desc) rk
	from employee
) e1 where rk >= 2
order by id, month desc;

解析

这三种写法都是一样的，上面两种是通过 not in 的方式去除掉当前最大月份，第三种是通过使用 row_number() 窗口函数去掉最大月份。

SQL：方法三

select
	e1.id id, e1.month month,
	sum(e2.salary) salary
from employee e1, employee e2
where e1.id = e2.id
and e1.month >= e2.month
and e1.month < e2.month + 3
and (e1.id, e1.month) not in (
	select id, max(month) month from employee group by id
)
group by e1.id, e1.month
order by e1.id, e1.month desc;

1. 两表查询 employee e1, employee e2

2. 查询条件 e1.id = e2.id 这好理解，e1.month >= e2.month and e1.month < e2.month + 3 怎么理解呢，最近三个月就加 3 吗？这就要配合 sum(e2.salary) 来理解了

   1. 我们先看第一个筛选条件 e1.id = e2.id，只需要两个相同 id 的数据

      select
      	e1.id, e1.month,
      	any_value(e1.salary),
      	any_value(e2.id),
      	any_value(e2.month),
      	any_value(e2.salary),
      	sum(e2.salary) salary,
      	group_concat(e2.salary),
      from employee e1, employee e2
      where e1.id = e2.id
      group by e1.id, e1.month
      order by e1.id, e1.month desc;

      

      从输出结果可以看出 e1.id = 1, e1.month = 4 的 salary 是由 group_concat(e2.salary) 的 4 个数相加，这 4 个值是 e2.id = 1, e2.month = 4,3,2,1 的 salary 。

   2. 增加筛选条件 e1.month >= e2.month，累加当前月份的 salary 和之前所有所有月份的 salary

      select
      	e1.id, e1.month,
      	any_value(e1.salary),
      	any_value(e2.id),
      	any_value(e2.month),
      	any_value(e2.salary),
      	sum(e2.salary) salary,
      	group_concat(e2.salary),
      from employee e1, employee e2
      where e1.id = e2.id
      and e1.month > e2.month
      group by e1.id, e1.month
      order by e1.id, e1.month desc;

      

      从结果中可以看出 e1.id = 1, e1.month = 2 的 salary 由 group_concat(e2.salary) 的 2 个数相加，这两个值是有 e2.id = 1, e2.month = 2,1 的 salary 。

   3. 增加筛选提条件 e1.month < e2.month + 3，只累加当前月份以及前 2 个月的 salary，这一步再加个 group_concat(e2.month) ，帮助我们更好理解。

      select
      	e1.id, e1.month,
      	any_value(e1.salary),
      	any_value(e2.id),
      	any_value(e2.month),
      	any_value(e2.salary),
      	sum(e2.salary) salary,
      	group_concat(e2.salary),
      	group_concat(e2.month)
      from employee e1, employee e2
      where e1.id = e2.id
      and e1.month >= e2.month
      and e1.month < e2.month + 3
      group by e1.id, e1.month
      order by e1.id, e1.month desc;

      加 e1.month < e2.month + 3 ， group_concat(e2.month) 的结果

      

      加了 e1.month < e2.month + 3 ， group_concat(e2.month) 的结果

      

      通过第二步筛选，得到所有的数据 e1.month 一定大于等于 e2.month ，因为要的结果是最近三个月的 salary 累加，所以上面一张图中的 1,4,2,3 是不符合要求的。如何把这些月份过滤掉用的就是 e1.month < e2.month + 3 ，比如当前月份是 8 月份，那个最近的 3 个月是 8,7,6 ， 8<8+3, 8<7+3, 8<6+3 。

   4. 去除最大月份，分组查询一次就能得到。