ORACLE sql 查询获取前 3 个薪水 rownum 大于

ORACLE sql query for getting top 3 salaries rownum greater than

提问by Learner

I want to write a query to display employees getting top 3 salaries

我想写一个查询来显示获得前 3 名薪水的员工

  SELECT *
    FROM (SELECT salary, first_name
            FROM employees
        ORDER BY salary desc)
   WHERE rownum <= 3;

But I dont understand how this rownum is calculated for the nested query will this work or if it has problem ,request you to please make me understand:

但我不明白如何为嵌套查询计算这个 rownum 是否有效，或者如果它有问题，请您让我理解：

SELECT *
  FROM (SELECT salary, first_name 
          FROM employees
      ORDER BY salary )
 WHERE rownum >= 3;

I went through this link Oracle/SQL: Why does query "SELECT * FROM records WHERE rownum >= 5 AND rownum <= 10" - return zero rows,but it again points to a link, which does not gives the answer

我浏览了这个链接Oracle/SQL: Why does query "SELECT * FROM records WHERE rownum >= 5 AND rownum <= 10" - return zero rows，但它再次指向一个链接，它没有给出答案

回答by A.B.Cade

a_horse_with_no_name's answer is a good one,
but just to make you understand why you're 1st query works and your 2nd doesn't:

a_horse_with_no_name 的回答很好，
但只是为了让您了解为什么第一个查询有效而第二个查询无效：

When you use the subquery, Oracle doesn't magicallyuse the rownum of the subquery, it just gets the data ordered so it gives the rownum accordingly, the first row that matches criteria still gets rownum 1 and so on. This is why your 2nd query still returns no rows.

当您使用子查询时，Oracle 不会神奇地使用子查询的 rownum ，它只是对数据进行排序，因此它相应地给出了 rownum，匹配条件的第一行仍然获得 rownum 1，依此类推。这就是您的第二个查询仍然不返回任何行的原因。

If you want to limit the starting row, you need to keep the subquery's rownum, ie:

如果要限制起始行，则需要保留子查询的rownum，即：

SELECT *
FROM (SELECT * , rownum rn
  FROM (SELECT salary, first_name
          FROM employees
      ORDER BY salary ) )sq
WHERE sq.rn >= 3;

But as a_horse_with_no_name said there are better options ...

但正如 a_horse_with_no_name 所说，有更好的选择......

EDIT:To make things clearer, look at this query:

编辑：为了让事情更清楚，看看这个查询：

with t as (
select 'a' aa, 4 sal from dual
union all
select 'b' aa, 1 sal from dual
union all
select 'c' aa, 5 sal from dual
union all
select 'd' aa, 3 sal from dual
union all
select 'e' aa, 2 sal from dual
order by aa
)
select sub.*, rownum main_rn 
  from (select t.*, rownum sub_rn from t order by sal) sub 
 where rownum < 4

note the difference between the sub rownum and the main rownum, see which one is used for criteria

注意子rownum和主rownum的区别，看哪一个用于标准

回答by a_horse_with_no_name

The "rownum" of a query is assigned beforean order by is applied to the result. So the rownumw 42 could wind up being the first row.

查询的“rownum”在order by 应用于结果之前分配。所以 rownumw 42 可能会成为第一行。

Generally speaking you need to use the rownum from the inner query to limit your overall output. This is very well explained in the manual:

一般来说，您需要使用内部查询中的 rownum 来限制您的整体输出。这在手册中有很好的解释：

http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns009.htm#i1006297

http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns009.htm#i1006297

I prefer using row_number() instead, because you have much better control over the sorting and additionally it's a standard feature that works on most modern DBMS:

我更喜欢使用 row_number() 代替，因为您可以更好地控制排序，而且它是适用于大多数现代 DBMS 的标准功能：

SELECT *
FROM (
  SELECT salary, 
         first_name, 
         row_number() over (order by salary) as rn
  FROM employees
)
WHERE rn <= 3
ORDER BY salary;

You should understand that the derived table in this case is only necessary to be able to apply a condition on the generated rncolumn. It's not there to avoid the "rownum problem" as the value of row_number()onlydepends on the order specifiy in the over(...)part (it is independent of any ordering applied to the query itself)

您应该明白，在这种情况下，派生表只需要能够对生成的rn列应用条件。这并不是为了避免“rownum 问题”，因为 的值row_number()仅取决于over(...)部分中指定的顺序（它与应用于查询本身的任何排序无关）

Note this would not return employees that have the same salary and would still fall under the top three. In that case using dense_rank()is probably more approriate.

请注意，这不会返回具有相同薪水但仍属于前三名的员工。在这种情况下，使用dense_rank()可能更合适。

回答by ShoeLace

if you want to select the people with the top 3 salaries.. perhaps you should consider using analytics.. something more like

如果你想选择薪水前三名的人......也许你应该考虑使用分析......更像是

SELECT *
FROM (
    SELECT salary, first_name, dense_rank() over(order by salary desc) sal_rank
    FROM employees 
)
WHERE  sal_rank <= 3

ie ALL people with the 3rd highest(ranked) salary amount(or more)

即所有具有第三高（排名）工资金额（或更多）的人

the advantage of this over using plain rownum is if you have multiple people with the same salary they will all be returned.

与使用普通 rownum 相比，这样做的优势在于，如果您有多个薪水相同的人，他们都会被退回。

回答by New_User

Easiest way to print 5th highest salary.

打印第五高薪水的最简单方法。

SELECT MIN(SALARY) FROM (SELECT SALARY FROM EMPLOYEES ORDER BY DESC) WHERE ROWNUM BETWEEN 1 AND 5 

according to same if u want to print 3rd or 4th highest salary then just chage last value.(means instead of 5 use 3 or 4 you will get 3rd or 4th highest salary).

同样，如果你想打印第 3 或第 4 高的薪水，则只需更改最后一个值。（意味着而不是 5 使用 3 或 4，您将获得第 3 或第 4 高的薪水）。

SELECT MIN(SALARY) FROM (SELECT SALARY FROM EMPLOYEES ORDER BY DESC) WHERE ROWNUM BETWEEN 1 AND 4

SELECT MIN(SALARY) FROM (SELECT SALARY FROM EMPLOYEES ORDER BY DESC) WHERE ROWNUM BETWEEN 1 AND 3

回答by sohail

SELECT EMPNO, SAL, (SELECT SUM(E.SAL) FROM TEST E WHERE E.EMPNO <= T.EMPNO) R_SAL FROM (SELECT EMPNO, SAL FROM TEST ORDER BY EMPNO) T

SELECT EMPNO, SAL, (SELECT SUM(E.SAL) FROM TEST E WHERE E.EMPNO <= T.EMPNO) R_SAL FROM (SELECT EMPNO, SAL FROM TEST ORDER BY EMPNO) T

回答by ANAND

Easiest way to find the top 3 employees in oracle returning all fields details:

在返回所有字段详细信息的 oracle 中查找前 3 名员工的最简单方法：

SELECT *
FROM (
    SELECT * FROM emp  
      ORDER BY sal DESC)
WHERE rownum <= 3 ;

回答by Manikanth Pedapudi

select * 
from (
    select emp.*, 
           row_number() over(order by sal desc)r 
    from emp
) 
where r <= 3;

回答by Priya Nimodia

SELECT Max(Salary)
FROM Employee
WHERE Salary < (SELECT Max(salary) FROM employee WHERE Salary NOT IN (SELECT max(salary) FROM employee))
ORDER BY salary DESC;