如何使用SQLite查找部门第n大的工资

Question

假设所有员工及其部门都列在同一个表employees中。你如何找到按部门排名第n高的薪水?

我知道你已经很接近了。

select SAL from EMPLOYEE E1 where 
(N - 1) = (select count(distinct(SAL)) 
from EMPLOYEE E2 
where E2.SAL > E1.SAL )

但是添加groupby仍然不能让你得到它，除非你对条件进行连接?

select SAL from EMPLOYEE E1
where 
(N - 1) = (select count(distinct(SAL)) 
from EMPLOYEE E2 
inner join EMPLOYEE E3 ON department_id
where E2.SAL > E3.SAL )
groupby department

Answer 1

如果这是作业，你必须使用关联子查询那么你必须关联department:

SELECT DISTINCT e1.department, e1.SAL 
FROM EMPLOYEE e1 
WHERE (N - 1) = (
SELECT COUNT(DISTINCT e2.SAL) 
FROM EMPLOYEE e2 
WHERE e2.department = e1.department AND e2.SAL > e1.SAL 
);

一个更好的解决方案是使用DENSE_RANK()窗口函数:

SELECT DISTINCT department, SAL
FROM ( 
SELECT *, DENSE_RANK() OVER (PARTITION BY department ORDER BY SAL DESC) dr
FROM EMPLOYEE 
)
WHERE dr = N;