今天看看这两道leetcode数据库题目
第一道:
Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
解答
① 用排名函数先对每个人的薪资分部门来排个名先:
SELECT
Name,
Salary,
DepartmentId,
dense_rank() over (partition by DepartmentId order by Salary desc)
FROM
Employee
② 得到排名信息后,我们还差一个部门名字,所以要拿上面的表连接Department表
SELECT
b.Name Department,
a.Name Employee,
a.Salary Salary
FROM
(
SELECT
Name,
Salary,
DepartmentId,
dense_rank() over (partition by DepartmentId order by Salary desc) as rank
FROM
Employee
) a JOIN Department b on a.DepartmentId = b.Id
WHERE
a.rank = 1 # 把各部门的第一个都给找出来
过了!看看排名,不过得在MS SQL Server才能用窗口函数。leetcode的Mysql还不支持,得8.0版本以上才可以,
利用 变量和IF语句 自己实现dense_rank()
SELECT
b.Name Department,
a.Name Employee,
a.Salary Salary
FROM
(
SELECT
Name,
Salary,
DepartmentId,
if(@pre_DepartmentId = DepartmentId,
if(@pre_Salary = Salary,@cur_rank, @cur_rank := @cur_rank + 1),
@cur_rank:=1) as rank
@pre_Salary := Salary,
@pre_DepartmentId := DepartmentId
FROM
Employee,(SELECT @cur_rank := 0,@pre_DepartmentId := null,@pre_Salary := null)
ORDER BY
DepartmentId,
Salary
) a JOIN Department b on a.DepartmentId = b.Id
WHERE
a.rank = 1
利用 半连接
SELECT
d.Name as Department,
e1.Name as Employee,
e1.Salary as Salary
FROM
Employee e1 JOIN Department d on e1.DepartmentId = d.Id
WHERE
e1.Salary =
(
SELECT
MAX(e2.Salary)
FROM
Employee e2
WHERE
e2.DepartmentId = e1.DepartmentId
)
第二道:
那其实知道了第一道,第二道的思路就很自然了,变成in(1,2,3 )就行
SELECT
b.Name Department,
a.Name Employee,
a.Salary Salary
From (
SELECT
Name,
Salary,
DepartmentId,
DENSE_RANK() over(PARTITION BY DepartmentId ORDER BY Salary Desc) AS rank
From
Employee
) As a join Department b on a.DepartmentId =b.Id
Where
a.rank in(1,2,3)