Leetcode185.部门工资前三高的所有员工（困难）
2020-07-10 本文已影响0人
kaka22
题目
Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释：
IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。
审题
思路 给Employee进行分组排序即可 然后选取排名小于等于三的即可 再与department表连接
解答
我的数据可能和题目有些出入 但是不影响
SELECT *
FROM employee2;
之前做过分组排序的问题
SELECT E.`DepartmentId`, E.`NAME`, E.`Salary`,
IF(@pre_dep=E.`DepartmentId`, @rank:=@rank+1, @rank:=1) AS rank,
@pre_dep:= E.`DepartmentId`
FROM employee2 AS E, (SELECT @rank:=0, @pre_dep:=NULL)init
ORDER BY E.`DepartmentId`, E.`Salary` DESC;
但我们注意到1部门的第三工资为70000应该有两名 所以这里需要同分同名的排序 所以需要进行一些修正。
加入一个变量pre_sal
逻辑就是当当前dep与pre_dep相同时 可能加一也可能不变 这时需要判断与前一工资是否相同 相同则rank不变 不同则加一
SELECT E.`DepartmentId`, E.`NAME`, E.`Salary`,
IF(@pre_dep=E.`DepartmentId`, IF(@pre_sal=E.`Salary`, @rank:=@rank, @rank:=@rank +1), @rank:=1) AS rank,
@pre_dep:= E.`DepartmentId`,
@pre_sal:= E.`Salary`
FROM employee2 AS E, (SELECT @rank:=0, @pre_dep:=NULL, @pre_sal:=0)init
ORDER BY E.`DepartmentId`, E.`Salary` DESC;
再两表连接选出需要的字段即可
SELECT D.`Name`, tmp.`NAME`, tmp.`Salary`
FROM (SELECT E.`DepartmentId`, E.`NAME`, E.`Salary`,
IF(@pre_dep=E.`DepartmentId`, IF(@pre_sal=E.`Salary`, @rank:=@rank, @rank:=@rank +1), @rank:=1) AS rank,
@pre_dep:= E.`DepartmentId`,
@pre_sal:= E.`Salary`
FROM employee2 AS E, (SELECT @rank:=0, @pre_dep:=NULL, @pre_sal:=0)init
ORDER BY E.`DepartmentId`, E.`Salary` DESC) AS tmp
JOIN department AS D
ON tmp.`DepartmentId` = D.`Id`
WHERE tmp.rank <=3
本地可以运行出正确结果 结果在Leetcode无法提交。。。 服了
SELECT dpTable.Name AS Department, Employee, Salary
FROM
(SELECT Name AS Employee, Salary, DepartmentId,
@rank := IF(@preDepartmentId = DepartmentId, IF(@preSalary = Salary, @rank + 0, @rank + 1), 1) AS SalaryRank,
@preDepartmentId := DepartmentId, @preSalary := Salary
FROM Employee, (SELECT @preDepartmentId := NULL, @preSalary := NULL, @rank := 0) AS Init
ORDER BY DepartmentId, Salary DESC) AS RankTable
INNER JOIN Department AS dpTable ON RankTable.DepartmentId = dpTable.Id
WHERE SalaryRank <= 3;
别的方法
1.方法一
先用两表连接
SELECT E.`NAME`, E.`Salary`, D.`Name`
FROM employee2 AS E
JOIN department AS D
ON E.`DepartmentId` = D.`Id`
然后对其进行筛选 选取部门相同的大于其工资个数的人数 若小于等于2则就是前三名
SELECT E.`NAME`, E.`Salary`, D.`Name`
FROM employee2 AS E
JOIN department AS D
ON E.`DepartmentId` = D.`Id`
WHERE(
SELECT COUNT(DISTINCT E1.`Salary`)
FROM employee2 AS E1
WHERE E1.`DepartmentId` = E.`DepartmentId` AND E.`Salary` < E1.`Salary`) <= 2;
感觉好强啊。。。
2.方法二
先找出每个部门薪水第三高的薪水A。每个人的薪水只要大于等于A，就是所要结果。
SELECT *
FROM Employee2 e1
LEFT JOIN Employee2 e2 ON(e1.DepartmentId=e2.DepartmentId AND e1.Salary>e2.Salary)
LEFT JOIN Employee2 e3 ON(e2.DepartmentId=e3.DepartmentId AND e2.Salary>e3.Salary)
注意：上面的小于号的顺序，e1.Salary>e2.Salary>e3.Salary。这个顺序非常得重要。
得出这样的结果：
从结果中发现，求第三高的薪水，只能在e3.Salary上求max。且要处理 e3.Salary 为null的情况。如果在 e1.Salary 上求max，得到的一定是每个部门的最高薪水。因此left join 左边的表的所有元组必然在结果中。
用CASE WHEN END子句，对null字段进行处理。
在这里当值为NULL时，将其替换为0。
SELECT e1.DepartmentId,
CASE
WHEN MAX(e3.salary) IS NULL THEN 0
ELSE MAX(e3.salary)
END AS max_salary
FROM Employee2 e1
LEFT JOIN Employee2 e2 ON(e1.DepartmentId=e2.DepartmentId AND e1.Salary>e2.Salary)
LEFT JOIN Employee2 e3 ON(e2.DepartmentId=e3.DepartmentId AND e2.Salary>e3.Salary)
GROUP BY e1.DepartmentId
两表连接得到最后的结果
SELECT D.name AS `Department`,E.name AS `Employee`,E.Salary
FROM Employee2 AS E
JOIN Department AS D ON (E.departmentid = D.id)
JOIN (
SELECT e1.DepartmentId, CASE WHEN MAX(e3.salary) IS NULL THEN 0 ELSE MAX(e3.salary) END AS m
FROM Employee2 e1
LEFT JOIN Employee2 e2 ON(e1.DepartmentId=e2.DepartmentId AND e1.Salary>e2.Salary)
LEFT JOIN Employee2 e3 ON(e2.DepartmentId=e3.DepartmentId AND e2.Salary>e3.Salary)
GROUP BY e1.DepartmentId
) AS F ON (E.departmentid = F.departmentid AND E.salary >= F.m)
这个方法求第三高的薪水需要连接三个表且需要处理NULL 也不方便推广到N个的情况
下面尝试用定义变量的方式做一下这个问题
先对每个部门的薪资进行排序
SELECT E.salary, E.departmentId,
IF(E.departmentId = @pre_dep, @rank:=@rank+1, @rank:=1) AS ranak,
@pre_dep:= E.departmentId
FROM employee2 AS E, (SELECT @rank:=0, @pre_dep:=NULL) AS inti
ORDER BY E.departmentId, E.salary DESC;
取出每个部门第三高的工资
由于有的部门可能只有两个人 所以最好是选排名小于等于3中的最小值作为每个部门薪资第三的薪资
SELECT tmp.departmentId, MIN(tmp.salary) AS min_sal
FROM (SELECT E.salary, E.departmentId,
IF(E.departmentId = @pre_dep, @rank:=@rank+1, @rank:=1) AS rank,
@pre_dep:= E.departmentId
FROM employee2 AS E, (SELECT @rank:=0, @pre_dep:=NULL) AS inti
ORDER BY E.departmentId, E.salary DESC) AS tmp
WHERE tmp.rank <=3
GROUP BY tmp.departmentId;
两表连接
SELECT dep.`Name` AS `Department`,EE.`NAME` AS `Employee`, EE.`Salary`
FROM employee2 AS EE
JOIN department AS dep
ON EE.`DepartmentId` = dep.`Id`
JOIN (SELECT tmp.departmentId, MIN(tmp.salary) AS min_sal
FROM (SELECT E.salary, E.departmentId,
IF(E.departmentId = @pre_dep, @rank:=@rank+1, @rank:=1) AS rank,
@pre_dep:= E.departmentId
FROM employee2 AS E, (SELECT @rank:=0, @pre_dep:=NULL) AS inti
ORDER BY E.departmentId, E.salary DESC) AS tmp
WHERE tmp.rank <=3
GROUP BY tmp.departmentId) tmp
ON tmp.departmentId = EE.`DepartmentId` AND EE.`Salary` >= tmp.min_sal
