LeetCode-579. 查询员工的累计薪水（困难）用户变量

最新推荐文章于 2024-07-26 18:10:25 发布

红楼终究一场梦

最新推荐文章于 2024-07-26 18:10:25 发布

阅读量706

点赞数 1

分类专栏：语言数据库

本文链接：https://blog.csdn.net/Hello_JavaScript/article/details/103363074

版权

语言同时被 2 个专栏收录

137 篇文章 0 订阅

订阅专栏

数据库

106 篇文章 2 订阅

订阅专栏

Employee 表保存了一年内的薪水信息。

请你编写 SQL 语句，来查询一个员工三个月内的累计薪水，但是不包括最近一个月的薪水。

结果请按 'Id' 升序，然后按 'Month' 降序显示。

示例：
输入：

| Id | Month | Salary |
|----|-------|--------|
| 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 |
输出：

| Id | Month | Salary |
|----|-------|--------|
| 1 | 3 | 90 |
| 1 | 2 | 50 |
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 3 | 3 | 100 |
| 3 | 2 | 40 |

解释：

员工 '1' 除去最近一个月（月份 '4'），有三个月的薪水记录：月份 '3' 薪水为 40，月份 '2' 薪水为 30，月份 '1' 薪水为 20。

所以近 3 个月的薪水累计分别为 (40 + 30 + 20) = 90，(30 + 20) = 50 和 20。

| Id | Month | Salary |
|----|-------|--------|
| 1 | 3 | 90 |
| 1 | 2 | 50 |
| 1 | 1 | 20 |
员工 '2' 除去最近的一个月（月份 '2'）的话，只有月份 '1' 这一个月的薪水记录。

| Id | Month | Salary |
|----|-------|--------|
| 2 | 1 | 20 |
员工 '3' 除去最近一个月（月份 '4'）后有两个月，分别为：月份 '4' 薪水为 60 和月份 '2' 薪水为 40。所以各月的累计情况如下：

| Id | Month | Salary |
|----|-------|--------|
| 3 | 3 | 100 |
| 3 | 2 | 40 |

题目来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/find-cumulative-salary-of-an-employee

解法一

首先要明确“三个月内”是什么意思？

设当前月为a，则a，a-1，a-2为“三个月内”。

这种“前缀和”问题，通常采用自连接。

先找出每个月的“三个月内”的全部月份。

SELECT *
FROM employee AS E1 JOIN employee AS E2 ON (E1.Id = E2.Id AND E1.MONTH >= E2.MONTH and E2.MONTH >= E1.MONTH - 3)

其中：

E1.MONTH >= E2.MONTH and E2.MONTH >= E1.MONTH - 3

表示E1.MONTH的“三个月内”的全部月份。

再计算每个月三个月内的薪水累加和。

group by按id和month分组。计算E2.Salary的累加和。结果命名为A。

(
	SELECT E1.Id,E1.MONTH,SUM(E2.Salary) AS `tsalary`
	FROM employee AS E1 JOIN employee AS E2 ON (E1.Id = E2.Id AND E1.MONTH >= E2.MONTH and E2.MONTH >= E1.MONTH - 2)
	GROUP BY E1.Id,E1.MONTH
) AS A

又因为不能包括最近一个月的薪水。

其实是要排除最高月份的值。

每个员工的最高月份。group by按id分组，最大月份值。

结果命名为B。

(
	SELECT E.Id, MAX(E.MONTH) AS `max_month`
	FROM employee AS E
	GROUP BY E.Id
) AS B

结合表A和表B，选出每个员工最高月份之前的值。

结果请按 ‘Id’ 升序，按 ‘Month’ 降序显示。

SELECT A.id,A.month,A.tsalary AS `Salary`
from
(
	SELECT E1.Id,E1.MONTH,SUM(E2.Salary) AS `tsalary`
	FROM employee AS E1 JOIN employee AS E2 ON (E1.Id = E2.Id AND E1.MONTH >= E2.MONTH and E2.MONTH >= E1.MONTH - 2)
	GROUP BY E1.Id,E1.MONTH
) AS A
JOIN 
(
	SELECT E.Id, MAX(E.MONTH) AS `max_month`
	FROM employee AS E
	GROUP BY E.Id
) AS B
	ON (A.Id=B.Id AND A.MONTH < B.max_month)
order by A.id,A.month desc


-- 自己写 --
SELECT A.Ids, A.Month, A.tsalary AS `Salary`
FROM(
SELECT E1.ids,E1.Month,SUM(E2.Salary) AS `tsalary`
FROM employee_579 AS E1 JOIN employee_579 AS E2 ON (E1.`Ids`= E2.`Ids` AND E1.`Month` >= E2.`Month` AND E2.`Month` >= E1.`Month` -2)
GROUP BY E1.Ids,E1.Month
)AS A
JOIN
(
SELECT E.`Ids`,MAX(E.`Month`)AS `max_month`
FROM employee_579 AS e
GROUP BY E.`Ids`
)AS B
ON (A.Ids = B.Ids AND A.Month < B.max_month)
ORDER BY A.`Ids`,A.`Month` DESC;

解法二

与解法一思路相同，既然只需要连续三个月的值。

干脆三个员工表自连接即可。

又由于1月份，2月份，向前不可能有三个月。

必然要用left join。

SELECT *
FROM employee AS E1
LEFT JOIN employee AS E2 ON (E1.Id = E2.Id AND E2.MONTH = E1.MONTH -1)
LEFT JOIN employee AS E3 ON (E3.Id = E2.Id AND E3.MONTH = E2.MONTH -1)

再将三个月的薪水相加。注意到E2.Salary和E3.Salary为NULL时，薪水值为0.

结果命名为A。

FROM
(
	SELECT E1.Id,E1.MONTH,(E1.Salary + if(E2.Salary IS NULL,0,E2.Salary) + if(E3.Salary IS NULL,0,E3.Salary)) AS `tsalary`
	FROM employee AS E1
	LEFT JOIN employee AS E2 ON (E1.Id = E2.Id AND E2.MONTH = E1.MONTH -1)
	LEFT JOIN employee AS E3 ON (E3.Id = E2.Id AND E3.MONTH = E2.MONTH -1)
) AS A

求最大月份如解法一。

(
	SELECT E.Id, MAX(E.MONTH) AS `max_month`
	FROM employee AS E
	GROUP BY E.Id
) AS B

之后同解法一，连接表A和表B，过滤掉最大月份值。

SELECT A.id,A.month,A.tsalary AS `Salary`
FROM
(
	SELECT E1.Id,E1.MONTH,(E1.Salary + if(E2.Salary IS NULL,0,E2.Salary) + if(E3.Salary IS NULL,0,E3.Salary)) AS `tsalary`
	FROM employee AS E1
	LEFT JOIN employee AS E2 ON (E1.Id = E2.Id AND E2.MONTH = E1.MONTH -1)
	LEFT JOIN employee AS E3 ON (E3.Id = E2.Id AND E3.MONTH = E2.MONTH -1)
) AS A
JOIN 
(
	SELECT E.Id, MAX(E.MONTH) AS `max_month`
	FROM employee AS E
	GROUP BY E.Id
) AS B
	ON (A.Id=B.Id AND A.MONTH < B.max_month)
order by A.id,A.month desc

解法三

员工表按id升序，再按salary升序。

应用用户变量，可计算三个月内薪水累加和。

定义用户变量：@pre_id——上一行的id，@pre_salary1——前一个月的薪水值，@pre_salary2——前两个月的薪水值。

(SELECT @pre_id:= NULL,@pre_salary1:=0,@pre_salary2:=0) AS T

每个人，三个月内累计薪水的逻辑：

if (员工id != @pre_id){
    三个月内累计薪水 = 员工id的薪水
}else{
    三个月内累计薪水 = 员工id的薪水 + @pre_salary1 + @pre_salary2
}

SQL代码：

IF(E.Id != @pre_id,
	E.Salary
,
	E.Salary + @pre_salary1 + @pre_salary2
) AS `Salary`

用户变量更新逻辑：

if (员工id != @pre_id)
{
    @pre_salary2 = 0
}
else{
    @pre_salary2 = @pre_salary1
}

@pre_salary1 = 员工id的薪水
@pre_id = 员工id的id。

SQL代码：

IF(E.Id != @pre_id,
	E.Salary
,
	E.Salary + @pre_salary1 + @pre_salary2
) AS `Salary`,
if(E.Id != @pre_id,
	@pre_salary2:=0
,
	@pre_salary2:=@pre_salary1
) AS `pre2`,
(@pre_salary1:=E.Salary) AS `pre1`,
@pre_id:=E.Id

注意：上述逻辑成立的前提—— 员工表按id升序，再按salary升序。

将上述逻辑合起来，结果命名为表A。

-- 首先按照降序排序
-- 然后按照从第一行向下执行
-- 每一行执行一遍下边sql，按照顺序更新变量。
-- 例如更新到id为1月份为4，继续执行id就不同了Salary就是id为2的第一个月。
(
	SELECT 
		E.Id,
		E.MONTH, 
		IF(E.Id != @pre_id,
			E.Salary
		,
			E.Salary + @pre_salary1 + @pre_salary2
		) AS `Salary`,
		if(E.Id != @pre_id,
			@pre_salary2:=0
		,
			@pre_salary2:=@pre_salary1
		) AS `pre2`,
		(@pre_salary1:=E.Salary) AS `pre1`,
		@pre_id:=E.Id
	FROM employee AS E,
	(SELECT @pre_id:= NULL,@pre_salary1:=0,@pre_salary2:=0) AS T
	ORDER BY E.Id,E.MONTH
) AS A

降序排序后的数据：根据两张图上数据可以看出含有变量的sql的执行顺序。

SELECT * FROM employee_579 AS E ORDER BY E.Ids,E.Month

每个员工的最大月份类似解法一。

(
	SELECT E.Id, MAX(E.MONTH) AS `max_month`
	FROM employee AS E
	GROUP BY E.Id
) AS B

之后同解法一，连接表A和表B，过滤掉最大月份值。

SELECT A.Id,A.MONTH,A.salary
FROM
(
	SELECT 
		E.Id,
		E.MONTH, 
		IF(E.Id != @pre_id,
			E.Salary
		,
			E.Salary + @pre_salary1 + @pre_salary2
		) AS `Salary`,
		if(E.Id != @pre_id,
			@pre_salary2:=0
		,
			@pre_salary2:=@pre_salary1
		) AS `pre2`,
		(@pre_salary1:=E.Salary) AS `pre1`,
		@pre_id:=E.Id
	FROM employee AS E,
	(SELECT @pre_id:= NULL,@pre_salary1:=0,@pre_salary2:=0) AS T
	ORDER BY E.Id,E.MONTH
) AS A
JOIN 
(
	SELECT E.Id, MAX(E.MONTH) AS `max_month`
	FROM employee AS E
	GROUP BY E.Id
) AS B
	ON (A.Id=B.Id AND A.MONTH < B.max_month)
order by A.id,A.month desc

红楼终究一场梦

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
LeetCode-579. 查询员工的累计薪水（困难）用户变量

Employee 表保存了一年内的薪水信息。请你编写 SQL 语句，来查询一个员工三个月内的累计薪水，但是不包括最近一个月的薪水。结果请按 'Id' 升序，然后按 'Month' 降序显示。示例：输入：| Id | Month | Salary ||----|-------|--------|| 1 | 1 | 20 || 2 | 1 | 20 ...
复制链接

扫一扫

专栏目录