时间戳

首页 > 解决方案 > SQL中的第N个薪水

sql - SQL中的第N个薪水

问题描述

我试图了解下面的查询，它是如何工作的。

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
               SELECT COUNT(DISTINCT(Emp2.Salary))
               FROM Employee Emp2
               WHERE Emp2.Salary > Emp1.Salary
               )

假设我有5不同的薪水，并希望获得3rd最高的薪水。那么内部查询将首先运行然后外部查询？

我很困惑它是如何在 sql 引擎中完成的。很想知道。因为如果它3rd最大 then 3-1 = 2，所以也2需要与内部计数匹配。如何操作内部计数。

谁能解释它的工作原理..？

标签： sqloraclesubquerygreatest-n-per-group

解决方案

The subquery is correlated subquery, so it conceptually executes once for each row in the outer query (database optimizations left apart).. What it does is count how many employees have a salary greater than the one on the row in the outer query: if there are 2 employee with a higher salary, then you know that the employee on the current row in the outer query has the third highest salary.

Another way to phrase this is to use row_number() for this:

select *
from (
    select 
        e.*, 
        row_number() over(order by salary desc) rn 
    from employee e
) t
where rn = 3

Depending on how you want to handle duplicates, dense_rank() might also be an option.

推荐阅读