sql查询:部门工资前三高的员工和部门工资最高的员工

建表语句:

Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Id int, Name varchar(255));
Truncate table Employee;
insert into Employee (Id, Name, Salary,DepartmentId) values ('1', 'Joe', '70000', '1');
insert into Employee (Id, Name, Salary,DepartmentId) values ('2', 'Henry', '80000', '2');
insert into Employee (Id, Name, Salary,DepartmentId) values ('3', 'Sam', '60000', '2');
insert into Employee (Id, Name, Salary,DepartmentId) values ('4', 'Max', '90000', '1');

insert into Employee (Id, Name, Salary,DepartmentId) values ('5', 'Randy', '85000', '1');
Truncate table Department;
insert into Department (Id, Name) values('1', 'IT');
insert into Department (Id, Name) values('2', 'Sales');

表结构如下：

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 |
| 5 | Randy | 85000 | 1 |
+------+-------+--------+--------------+

Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+

部门工资前三高的员工

编写一个 SQL 查询，找出每个部门工资前三高的员工。
例如，根据上述给定的表格，查询结果应返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+

答案代码：

SELECT D.Name, E1.Name, E1.Salary
FROM Employee as E1
JOIN Department as D
ON E1.DepartmentId = D.Id
WHERE 3 > (
    SELECT COUNT(DISTINCT E2.Salary) 
    FROM Employee AS E2
    WHERE E2.Salary > E1.Salary AND E1.DepartmentId = E2.DepartmentId
)
ORDER BY Department.Name, e1.Salary DESC;

解析:

不妨假设e1=e2=[6,5,4,3]，则子查询的过程如下：
1、e1.Salary=3；则e2.Salary可以取4、5、6；COUNT(DISTINCT e2.Salary)=3
2、e1.Salary=4；则e2.Salary可以取5、6；COUNT(DISTINCT e2.Salary)=2
3、e1.Salary=5；则e2.Salary可以取6；COUNT(DISTINCT e2.Salary)=1
4、e1.Salary=6；则e2.Salary无法取值；COUNT(DISTINCT e2.Salary)=0
则要令COUNT(DISTINCT e2.Salary) < 3 的情况有上述的4、3、2.
也即是说，这等价于取e1.Salary最大的三个值。

部门工资最高的员工:

方法1:

select 
    d.Name,e.Name,e.Salary
from 
    Department d,Employee e
where 
    e.DepartmentId=d.Id and 
    e.Salary=(
        Select max(Salary) from Employee where DepartmentId = d.Id
    )

方法2:

select
    d.Name,e.Name,e.Salary 
from 
    Employee e join Department d on e.DepartmentId=d.Id 
where 
    (e.Salary,e.DepartmentId) in (select max(Salary),DepartmentId from Employee group by DepartmentId)

---

欢迎大家关注我的公众号

半亩房顶