ORACLE SQL查询以获取前3名薪水rownum大于 [英] ORACLE sql query for getting top 3 salaries rownum greater than

问题描述

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

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

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

但是我不明白如何为嵌套查询计算该rownum 这项工作是否可行，如果有问题，请您让我理解:

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:

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

推荐答案

a_horse_with_no_name的答案很好，
但是只是为了让您理解为什么您是第一个查询而第二个却不行:

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:

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

When you use the subquery, Oracle doesn't magically use 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.

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

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

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

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

But as a_horse_with_no_name said there are better options ...

为了使情况更清楚，请看以下查询:

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

这篇关于ORACLE SQL查询以获取前3名薪水rownum大于的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！