SQL 如何在数据库Oracle中选择工资第二高的记录？

How can I select the record with the 2nd highest salary in database Oracle?

提问by user1801838

Suppose I have a table employee with id, user_name, salary. How can I select the record with the 2nd highest salary in Oracle?

假设我有一个带有 id、user_name、salary 的员工表。如何在Oracle中选择工资第二高的记录？

I googled it, find this solution, is the following right?:

我用谷歌搜索，找到这个解决方案，以下是正确的吗？：

select sal from
     (select rownum n,a.* from
        ( select distinct sal from emp order by sal desc) a)
where n = 2;

回答by Frank Schmitt

RANK and DENSE_RANK have already been suggested - depending on your requirements, you might also consider ROW_NUMBER():

已经建议了 RANK 和 DENSE_RANK - 根据您的要求，您还可以考虑 ROW_NUMBER()：

select * from (
  select e.*, row_number() over (order by sal desc) rn from emp e
)
where rn = 2;

The difference between RANK(), DENSE_RANK() and ROW_NUMBER() boils down to:

RANK()、DENSE_RANK() 和 ROW_NUMBER() 之间的区别归结为：

• ROW_NUMBER() always generates a unique ranking; if the ORDER BY clause cannot distinguish between two rows, it will still give them different rankings (randomly)
• RANK() and DENSE_RANK() will give the same ranking to rows that cannot be distinguished by the ORDER BY clause
• DENSE_RANK() will always generate a contiguous sequence of ranks (1,2,3,...), whereas RANK() will leave gaps after two or more rows with the same rank (think "Olympic Games": if two athletes win the gold medal, there is no second place, only third)

• ROW_NUMBER() 始终生成唯一的排名；如果 ORDER BY 子句不能区分两行，它仍然会给它们不同的排名（随机）
• RANK() 和 DENSE_RANK() 将对 ORDER BY 子句无法区分的行给出相同的排名
• DENSE_RANK() 将始终生成连续的排名序列 (1,2,3,...)，而 RANK() 将在具有相同排名的两行或更多行后留下空白（想想“奥运会”：如果两名运动员获胜金牌，没有第二名，只有第三名）

So, if you only want one employee (even if there are several with the 2nd highest salary), I'd recommend ROW_NUMBER().

因此，如果您只想要一名员工（即使有几名员工的薪水位居第二），我建议您使用 ROW_NUMBER()。

回答by hd1

If you're using Oracle 8+, you can use the RANK()or DENSE_RANK()functions like so

如果您使用的是 Oracle 8+，则可以像这样使用RANK()orDENSE_RANK()函数

SELECT *
FROM (
  SELECT some_column, 
         rank() over (order by your_sort_column desc) as row_rank
) t
WHERE row_rank = 2;

回答by Sanchit

This query works in SQL*PLUSto find out the 2nd Highest Salary-

此查询适用于SQL*PLUS，以找出第二高的薪水-

SELECT * FROM EMP
WHERE SAL = (SELECT MAX(SAL) FROM EMP
WHERE SAL < (SELECT MAX(SAL) FROM EMP));

This is double sub-query.

这是双重子查询。

I hope this helps you..

我希望这可以帮助你..

回答by John Woo

WITH records
AS
(
    SELECT  id, user_name, salary,
            DENSE_RANK() OVER (PARTITION BY id ORDER BY salary DESC) rn
    FROM    tableName
)
SELECT  id, user_name, salary
FROM    records 
WHERE   rn = 2

• DENSE_RANK()

• DENSE_RANK()

回答by Lingasamy Sakthivel

You should use something like this:

你应该使用这样的东西：

SELECT *
FROM (select salary2.*, rownum rnum from
         (select * from salary ORDER BY salary_amount DESC) salary2
  where rownum <= 2 )
WHERE rnum >= 2;

回答by shashank

select * from emp where sal=(select max(sal) from emp where sal<(select max(sal) from emp))

so in our emp table(default provided by oracle) here is the output

所以在我们的 emp 表（默认由 oracle 提供）这里是输出

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---

---

  7698 BLAKE      MANAGER         7839 01-MAY-81       3000            30
  7788 SCOTT      ANALYST         7566 19-APR-87       3000            20
  7902 FORD       ANALYST         7566 03-DEC-81       3000            20

or just you want 2nd maximum salary to be displayed

或者只是你想显示第二个最高工资

select max(sal) from emp where sal<(select max(sal) from emp)

MAX(SAL)

最大(萨尔)

  3000

回答by Md Wasi

select * FROM (
select EmployeeID, Salary
, dense_rank() over (order by Salary DESC) ranking
from Employee
)
WHERE ranking = 2;

dense_rank()is used for the salary has to be same.So it give the proper output instead of using rank().

Dense_rank()用于工资必须相同。因此它提供正确的输出而不是使用rank()。

回答by pradsav

select Max(Salary) as SecondHighestSalary from Employee where Salary not in (select max(Salary) from Employee)

选择 Max(Salary) 作为 SecondHighestSalary from Employee where Salary not in (select max(Salary) from Employee)

回答by Suraj Shewale

I would suggest following two ways to implement this in Oracle.

我建议采用以下两种方法在 Oracle 中实现这一点。

1. Using Sub-query:

1. 使用子查询：

select distinct SALARY   
from EMPLOYEE e1  
where 1=(select count(DISTINCT e2.SALARY) from EMPLOYEE e2 where         
  e2.SALARY>e1.SALARY);

This is very simple query to get required output. However, this query is quite slow as each salary in inner query is compared with all distinct salaries.

这是获取所需输出的非常简单的查询。然而，这个查询很慢，因为内部查询中的每个薪水都与所有不同的薪水进行比较。

1. Using DENSE_RANK():

2. 使用 DENSE_RANK()：

select distinct SALARY   
from
  (
    select e1.*, DENSE_RANK () OVER (order by SALARY desc) as RN 
    from EMPLOYEE e
  ) E
 where E.RN=2;

This is very efficient query. It works well with DENSE_RANK() which assigns consecutive ranks unlike RANK() which assigns next rank depending on row number which is like olympic medaling.

这是非常有效的查询。它适用于 DENSE_RANK()，它分配连续的排名，而 RANK() 则根据行号分配下一个排名，就像奥运会奖牌一样。

Difference between RANK() and DENSE_RANK(): https://oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions

RANK() 和 DENSE_RANK() 的区别：https: //oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions

回答by Vvn Bhaskar Vvn Bhaskar

SELECT * FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP));

SELECT * FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP));

(OR) SELECT ENAME ,SAL FROM EMP ORDER BY SAL DESC;

（或）从 SAL DESC 的 EMP 订单中选择 ENAME ,SAL；

(OR) SELECT * FROM(SELECT ENAME,SAL ,DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) R FROM EMP) WHERE R=2;

(或) SELECT * FROM(SELECT ENAME,SAL ,DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) R FROM EMP) WHERE R=2;