SQL查询找到第N个最高的工资 [英] SQL query to find Nth highest salary

问题描述

我指的是以下查询以查找员工的第N个最高工资。

I am referring to following query to find Nth highest salary of a employee.

select sal from emp t where &n = (select count(sal) from (select distinct sal 
 from emp) where t.sal<=sal);

一个绅士说这个查询工作。是否有人可以解释一下如何将COUNT（这真的是1到X之间的值，其中X是总不同的工资）与产生此结果？

One gentleman said this query works. Could someone please explain how equating a COUNT ( which really will be value between 1 to X where X is total distinct salaries) to &n produce this result ?

尝试了解数据库如何在内部处理此查询并生成结果？

I am trying to understand how database handles this query internally and produces result ?

谢谢。

推荐答案

首先，查询将返回 nth 最低工资值。要返回 nth 最高薪水值，您必须将 t.sal <= sal 更改为 t.sal> = sal 。

First, the query will return the nth lowest salary value. To return the nth highest salary value you must change t.sal <= sal to t.sal >= sal.

接下来，这个查询的工作原理是先找到不同的salary值列表作为一个派生表，然后确定在此列表中具有小于每个薪资的雇员的数量。 t.sal <= sal 使用派生表（大多数数据库需要有别名），并将每个值与外部 emp 表。应该注意，如果是tie，这将返回多行。

Next, this query works by first finding the distinct list of salary values as one derived table and then determines the number of employees that have a salary less than each one in this list. t.sal <= sal is taking the derived table (which most databases would require have an alias) and comparing each value against the outer emp table. It should be noted that this will return multiple rows in the case of a tie.

要手动跟踪输出，我们需要一些输入：

To manually trace the output, we need some inputs:

Alice       | 200
Bob         | 100
Charlie     | 200
Danielle    | 150

Select Distinct sal
From emp

/ p>

Gives us

200
100
150

现在我们分析外表中的每一行

Now we analyze each row in the outer table

Alice - There are 3 distinct salary values less than or equal to 200
Bob - 1 rows <= 100
Charlie - 3 rows <= 200
Danielle - 2 row <= 150

因此，对于每个薪水值，我们得到以下计数（并按计数重新排序）：

Thus, for each salary value we get the following counts (and reordered by count):

Bob 1
Danielle 2
Charlie 3
Alice 3

我认为你可以忽略的最重要的方面是外部 emp 表是 到内部计数计算（这就是为什么它被称为相关子查询）。即，对于外部 emp 表中的每一行，通过 t.sal <= sal 。同样，大多数数据库系统将需要最内部的查询具有这样的别名（注意 As Z 别名）：

The most important aspect that I think you are overlooking is that the outer emp table is correlated to the inner count calculation (which is why it is called a correlated subquery). I.e., for each row in the outer emp table, a new count is calculated for that row's salary via t.sal <= sal. Again, most database systems would require the inner most query to have an alias like so (note the As Z alias):

Select sal
From emp As t
Where &n =  (
            Select Count(Z.sal)
            From    (
                    Select Distinct sal
                    From emp
                    ) As Z
            Where t.sal <= Z.sal
            )

这篇关于SQL查询找到第N个最高的工资的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！