题目: 题目描述链接
解答:
select e1.name as 'Employee',e1.salary,d.name as 'Department'
from Employee e1 join Department d on d.id = e1.departmentId
where 3 > (
select count(distinct e2.salary) from Employee e2 where e1.salary < e2.salary
and e1.departmentId = e2.departmentId
)
讲解:
第一步: 先找出公司里前 3 高的薪水,意思是不超过三个值比这些值大
SELECT e1.Salary
FROM Employee AS e1
WHERE 3 >
(SELECT count(DISTINCT e2.Salary)
FROM Employee AS e2
WHERE e1.Salary < e2.Salary AND e1.DepartmentId = e2.DepartmentId) ;
举个栗子:
当 e1 = e2 = [4,5,6,7,8]
e1.Salary = 4,e2.Salary 可以取值 [5,6,7,8],count(DISTINCT e2.Salary) = 4
e1.Salary = 5,e2.Salary 可以取值 [6,7,8],count(DISTINCT e2.Salary) = 3
e1.Salary = 6,e2.Salary 可以取值 [7,8],count(DISTINCT e2.Salary) = 2
e1.Salary = 7,e2.Salary 可以取值 [8],count(DISTINCT e2.Salary) = 1
e1.Salary = 8,e2.Salary 可以取值 [],count(DISTINCT e2.Salary) = 0
最后 3 > count(DISTINCT e2.Salary),所以 e1.Salary 可取值为 [6,7,8],即集合前 3 高的薪水
第二步: 再把表 Department 和表 Employee 连接,获得各个部门工资前三高的员工。
SELECT
Department.NAME AS Department,
e1.NAME AS Employee,
e1.Salary AS Salary
FROM
Employee AS e1,Department
WHERE
e1.DepartmentId = Department.Id
AND 3 > (SELECT count( DISTINCT e2.Salary )
FROM Employee AS e2
WHERE e1.Salary < e2.Salary AND e1.DepartmentId = e2.DepartmentId )
ORDER BY Department.NAME,Salary DESC;