建表语句:
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)
欢迎大家关注我的公众号
