185. 部门工资前三高的所有员工
法一:利用子查询
SELECT
d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
Employee e1
JOIN
Department d
ON
e1.DepartmentId = d.Id
WHERE
3 > (SELECT
COUNT(DISTINCT e2.Salary)
FROM
Employee e2
WHERE
e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
)
;
法二:窗口函数
SELECT
Department,Employee,Salary
FROM
(
SELECT
d.name Department,e.name Employee,e.salary Salary,
dense_rank()
OVER(partition by d.name order by e.salary desc) rk
FROM
Employee e
LEFT JOIN
Department d
ON
e.departmentid = d.id
)t1
WHERE rk <= 3