Department Top Three Salaries_employees salaries department-优快云博客

本文链接：https://blog.youkuaiyun.com/codedz/article/details/105107083

本文介绍了一种SQL查询技巧，用于找出每个部门中薪资排名前三的员工，包括员工姓名、所属部门及薪资。通过自连接和子查询，巧妙地解决了这一复杂问题。

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

Department Top Three Salaries

description:
The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.

+----+-------+--------+--------------+
| 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            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows (order of rows does not matter).

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

Explanation:

In IT department, Max earns the highest salary, both Randy and Joe earn the second highest salary, and Will earns the third highest salary. There are only two employees in the Sales department, Henry earns the highest salary while Sam earns the second highest salary.

这道题给出了一个员工表，一个部门表，让找出每个不同部门里面前三高的工资的员工，包括员工姓名，所属部门，工资。
首先要求部门名和员工姓名，这两个属性属于不同的表，所以肯定要用到连接，直接按部门号连接两个表就行。
难点在于查询部门里工资前三高的员工，我的方法是用一个嵌套查询，通过这个嵌套查询按部门id自连接两张Employee表，查询出比外层查到的员工工资高的个数<3的工资的个数，即查出至多只有2个人比自己的工资高，也就是查到了每个部门里top3的工资和员工了。

Mycode:

# Write your MySQL query statement below
select d.name Department, e1.name Employee, e1.salary Salary
from Employee e1 join Department d on e1.departmentid = d.id
where (select count(distinct(e2.salary)) from Employee e2 
where e2.salary > e1.salary and e2.departmentid = e1.departmentid) < 3;

谢谢你的观看！