dense_rank()和max，哪个更好地找到第N个最高工资(如果没有结果，则返回null)

Question

例如N是2，我可以使用densr_rank((或max从Employee表中找到第二高的工资。

select max(salary) as SecondHighestSalary from employee 
where salary < (select max(salary) from employee) 

如果表中并没有第二高的薪水，则返回NULL，那么上面的查询非常有效。

然而，我可以使用dense_rank((函数来获得类似的结果，例如：

select e.salary as SecondHighestSalary 
from (
select salary, dense_rank() OVER (order by salary desc) dr from employee) e
where dr = 2  

考虑到表中只有一个第二高的工资，如果表中有工资[300, null, null]和返回的[null, null]，那么上面的查询很好，我只需要在结果集中有一个答案。我怎样才能做到这一点？

跟进问题：在这种情况下，哪一个更好(就内存/处理时间而言(max还是dense_rank((？

Answer 1

这是一个非常有趣的问题。您尚未指定数据库。但是，如果我假设您在salary上有一个可供查询使用的索引(因此可能是salary desc，具体取决于数据库(。那么您的第一个查询可能具有非常好的性能：

• 使用索引获取匹配薪资
• 开始扫描索引以查找小于最大值的值
• 短路扫描，因为你马上得到最大值

我并不保证所有数据库都会生成此计划，但在这种情况下，两个索引查找通常会比dense_rank()更快。

当然，对于这样的问题，您应该测试您的数据和数据库。这确实是正确的答案。

Answer 2

使用max的查询只能在N=2的情况下使用，所以这是一个糟糕的解决方案，但就性能而言，它应该比使用dense_rank的第二个查询更好。

我建议另一种方法：只获取按salary降序排列的N个第一行。然后，从这个结果中获得最低的工资。

如果您在salary列上有索引，那么获取N行的速度应该非常快。然后具有"；仅"；N行，也应该可以快速获取最后一条记录。

所以查询应该看起来像(对于SQL Server(：

SELECT TOP 1 * FROM (
SELECT TOP N * FROM SalaryTable ORDER BY salary DESC
) ORDER BY salary;

对于那些具有LIMIT N而不是SELECT TOP N的RDBMS：

SELECT * FROM (
SELECT * FROM SalaryTable ORDER BY salary DESC LIMIT N
) ORDER BY salary LIMIT 1;