SQL查询找到第N个最高薪水

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

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

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

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 ?

一位先生说这个查询有效。有人可以解释如何将COUNT(实际上是1到X之间的值,其中X是完全不同的工资)等同于产生这个结果?

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

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

Thank you.

9 个解决方案

#1

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.

首先,查询将返回第n个最低工资值。要返回第n个最高工资值,您必须将t.sal <= sal更改为t.sal> = sal。

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.

接下来,此查询的工作原理是首先查找工资值的不同列表作为一个派生表,然后确定薪水小于此列表中每个薪水的员工数。 t.sal <= sal正在获取派生表(大多数数据库需要具有别名)并将每个值与外部emp表进行比较。应该注意,在平局的情况下,这将返回多行。

To manually trace the output, we need some inputs:

要手动跟踪输出,我们需要一些输入:

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

Select Distinct sal
From emp

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

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):

我认为你忽略的最重要的方面是外部emp表与内部计数计算相关(这就是它被称为相关子查询的原因)。即,对于外部emp表中的每一行,通过t.sal <= sal计算该行的工资的新计数。同样,大多数数据库系统都要求最内层的查询具有这样的别名(注意As Z别名):

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
            )

#2

select sal 
from (
  select sal, 
         dense_rank() over (order by sal desc) as rnk
) t
where rnk = 5;

Replace where rnk = 5 with whatever "nth" you want.

用你想要的任何“nth”替换rnk = 5。

#3

To get the nth highest salary value just put the value of 'N'.

要获得第n个最高工资值,只需输入'N'的值即可。

Select Min(Salary) From (Select Top N * From Table_Name Order by Salary Desc);

#4

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always

#5

  SELECT Max(Salary) as Salary
    FROM employee
    where  Salary Not in 
    (SELECT TOP N Salary FROM employee ORDER BY Salary DESC)
  where N is defined by you.

So let's say you have the following salaries in the table employee: Here employeeID and Salary are the columns of employee table.

因此,假设您在表employee中有以下工资:这里employeeID和Salary是employee表的列。

EmployeeID Salary

 101  25,000
 154  89,000
 987  42,000
 450  12,000
 954  50,000

If we want to see the fourth-highest salary

如果我们想看到第四高薪

Salary

25,000

Query return fourth highest salary.

查询返回第四高薪。

#6

In the database record data entries like

在数据库记录数据条目中

employ_id    NAME     salary
101          Henry    24000
102          Smith    24000
105          Roy      17000  
106          Robbin   15000 
702          Mac      2500
708          Bill     2100
709          Kane     2000
710          Ted      2000

here Some of employees having same salary then how to calculate nth (highest/lowest)salary

这里有些员工的工资相同,如何计算第n(最高/最低)工资

for the calculation of 3rd highest salary

计算第3个最高薪水

select * from emloyees where salary in (select salary from (select rownum rank , salary from (select distinct salary from employees order by salary **desc**)) where rank =3;

---

ans = 15000

similarly to calculate 3rd lowest salary Type same Query with a small change instead of desc type asc

类似于计算第3个最低工资类型相同的查询使用较小的更改而不是desc类型asc

select * from emloyees where salary in (select salary from (select rownum rank , salary from (select distinct salary from employees order by salary **asc**)) where rank =3;

Hope This will help you

希望对你有帮助

#7

Change nth highest salary value just put the value of 'N'

改变第n个最高工资值只需加上'N'的值

SELECT e1.EmployeeName, e1.EmployeeSalary from Employee e1
where N = (
select COUNT(e2.EmployeeSalary) from Employee e2 where e2.EmployeeSalary >= e1.EmployeeSalary)

#8

There are so many ways to achieve this:-

有很多方法可以达到这个目的: -

1)

 Select Top(1) sal from emp 
    where sal not in (select DISTINCT top(n-1) sal from emp order by sal desc)

2)

select salary     
          from (
           select salary,
           roe_number() over (order by salary ) as row from emp
          ) emp1
  where row= n;

• This query will not work if multiple rows have the same values one after another.

如果多行具有相同的值,则此查询将不起作用。

3)

select salary     
              from (
               select salary,
               dense_rank() over (order by salary ) as row from emp
              ) emp1
      where row= n;

• This will create a unique row number for all unique salary amounts.

这将为所有唯一工资金额创建唯一的行号。

4)

 Select Min(sal) From 
       (Select DISTINCT Top n * From emp Order by sal Desc)as emp1;

5)

   SELECT * FROM emp Emp1
            WHERE (n-1) = (
                             SELECT COUNT(DISTINCT(Emp2.Sal))
                             FROM emp Emp2
                             WHERE Emp2.Sal > Emp1.Sal)

#9

Query:

select 
    ename  
    ,sal  
    ,dense_rank() over (order by sal desc) ranking  
from   emp;  

output:

ENAME   SAL   RANKING
KING    5000    1   
FORD    3000    2  
SCOTT   3000    2  
JONES   2975    3  
CLARK   2850    4  
BLAKE   2850    4  
ALLEN   1600    5  

Wrap a filter around and pick out the Nth highest salary, say the 4th highest salary.

包裹一个过滤器并挑选第N个最高薪水,说第四个最高薪水。

Query:

select *  
from  
(  
  select ename  
        ,sal  
        ,dense_rank() over (order by sal desc) ranking  
  from   emp  
)  
where ranking = 4 -- Replace 4 with any value of N  

output:

ENAME  SAL  RANKING
BLAKE  2850     4  
CLARK  2850     4