每个部门内前三高的工资,等同条件为该部门内比该工资级别高的工资级别少于3个
此类型的题目(TOPN题目),都可以转换成:
比…高的少于…个;
比…低的少于…个 的思路来解决
SELECT
d.`Name` AS 'Department',
e1.`Name` AS 'Employee',
e1.`Salary` AS 'Salary'
FROM
`Employee` e1
JOIN
`Department` d
ON
e1.`DepartmentId` = d.`Id`
/*
* 每个部门内有工资前三高的工资,等同条件为
* 该部门内比该工资级别高的工资级别少于3个
*/
WHERE
3 > ( SELECT
#可能有工资相等的人,这些都要并列排序
COUNT(DISTINCT e2.Salary)
FROM
Employee e2
WHERE
e2.Salary > e1.Salary
AND
e1.DepartmentId = e2.DepartmentId
)
#先按DepartmentId升序排序,DepartmentId相同情况下再按Salary降序排序
ORDER BY e1.`DepartmentId` ASC, e1.Salary DESC
;