SQL分组TopK问题

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