在“雇员表”中名列第三

Question

返回“员工表”中第三高薪资是否是正确的查询？

SELECT *
FROM employees
WHERE emp_no in (SELECT emp_no FROM employees ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY)
ORDER BY salary ASC
FETCH FIRST 1 ROWS ONLY

Answer 1

为了能够处理重复的值，我将使用窗口函数：

select *
from (
  select *, 
         dense_rank() over (order by salary desc) as rnk
  from employees
) t
where rnk = 3;

Answer 2

SELECT * FROM  employees ORDER BY salary DESC LIMIT 1 OFFSET 2;

查询按薪资对员工进行排序，并从结果集中第三行返回一个结果。我认为这将适用于MySQL和PostgreSQL。