sql查询找到第二个最高薪水 [英] sql query to find 2nd maximum salary

问题描述

我有一张这样的桌子。我想得到第二个最高工资，重复值也是







empid empname salary
1 srinivas 20000

3 suresh 20000

4 kevin 30000

2 simon 35000

5 sai 30000

6 ben 30000

7 sirish 30000





输出应该是

empid empname salary

4 kevin 30000

5 sai 30000



它应该只得到两个重复的第二个最大值

解决方案

避免DB特定代码（想到postgres偏移/限制）我得到：

  SELECT  *  FROM  T1 
  WHERE  SALARY = 
（ SELECT  MAX（SALARY） FROM  T1 
  WHERE  SALARY<> （ SELECT  MAX（SALARY） FROM  T1）
）; 

可能不是超级快，但也不是那么慢。



不能很好地扩展到第三，第四等但是你可以在内部联接中使用数据库特定的偏移量/限制（oracle等中的rownum）来选择第n个工资。



你提供的数据有4个人，工资为30000，所有数据都会被退回。

结果为

（

选择dense_rank（）over（按工资desc排序）为'SecondMaximum'，* from tablename

）



从结果中选择*，其中SecondMaximum = 2;

 选择 * 来自员工E 内部  join （ select  salary，row_number（） over （ order  按 salary  desc ）
  as  Rowno 来自员工）T   
 T.薪水= a.Salary 其中 T.Rowno = 2 

i have a table like this. i want to get the 2nd max salary with duplicate values also



empid empname salary
1 srinivas 20000
3 suresh 20000
4 kevin 30000
2 simon 35000
5 sai 30000
6 ben 30000
7 sirish 30000


the output should be
empid empname salary
4 kevin 30000
5 sai 30000

it should get only two duplicate 2nd max sal

解决方案

Avoiding DB specific code (postgres offset/limit comes to mind) I get:

SELECT * FROM T1
WHERE SALARY =
        (SELECT MAX(SALARY) FROM T1
           WHERE SALARY <> (SELECT MAX(SALARY) FROM T1)
        );

Might not be super fast, but not really that slow either.

Doesn't extend too well to third, fourth etc but then you could use database specific offset / limit (rownum in oracle etc) in the inner join to select the nth salary.

And the data you provide has 4 people with 30000 salary and all would be returned.

with result as
(
select dense_rank() over(order by salary desc) as 'SecondMaximum',* from tablename
)

select * from result where SecondMaximum=2;

select * from Employee E inner join (select  salary, row_number() over (order by salary desc)
 as Rowno from Employee)T on
T.Salary=a.Salary  where T.Rowno=2

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