Department Top Three Salaries

okiwilldoit

于 2016-03-22 14:42:07 发布

阅读量318

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库学习

本文链接：https://blog.youkuaiyun.com/okiwilldoit/article/details/50955035

数据库学习专栏收录该内容

33 篇文章

订阅专栏

本文介绍两种SQL方案来实现获取每个部门薪资排名前三的员工。第一种方法使用自连接及子查询来计算薪资排名；第二种方法则通过多表连接及子查询实现相同目标。两种方法均有效地解决了LeetCode上关于部门顶级薪资的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

表内自连接，得到rank前三的记录
https://leetcode.com/problems/department-top-three-salaries/

第一种：

SELECT D.Name AS Department,A.Employee,A.Salary FROM
(SELECT C.DepartmentId,C.Name AS Employee,C.Salary FROM
(SELECT E1.id,E1.Name,E1.Salary,E1.DepartmentId,COUNT(1) AS RANK FROM Employee E1
INNER JOIN 
(SELECT DISTINCT Salary,DepartmentId FROM Employee) E2
ON E1.DepartmentId=E2.DepartmentId AND E1.Salary<=E2.Salary
GROUP BY E1.id,E1.Name,E1.Salary,E1.DepartmentId) C WHERE C.RANK<4) A
INNER JOIN Department D ON D.Id=A.DepartmentId

第二种：

select d1.name as Department, 
       e1.name as Employee, 
       e1.salary as Salary 
from Department d1, Employee e1 
where (
    select count(distinct e2.salary) 
    from Employee e2 
    where e2.salary > e1.salary and e2.departmentid = e1.departmentid
) < 3 and d1.id = e1.departmentid;