具有所有字段的特定行号 [英] specific row number with all the fields

问题描述

我有HR oracle模式，在此，我想显示工资最高的员工的所有字段.

I have HR oracle schema and in that, I want to display all the fields of employees who have a 3rd highest salary.

这是我到目前为止所写的内容，但未返回任何内容.

Here is what I have written so far but it does not return anything.

select * from (
      select * from HR.EMPLOYEES 
       order by HR.EMPLOYEES.SALARY DESC) 
 where rownum=3

我想获得此结果而无需使用诸如density_rank()等任何函数.希望保持查询简单.但是，如果有人可以使用解析函数并可以解释其工作原理，那么到目前为止，我仍然可以理解其用法，但我不理解诸如density_rank()之类的函数的用法.对查询中缺少的内容有任何了解吗?

I want to get this result without using any functions like dense_rank() etc. Want to keep the query simple. But if someone can use analytic function and can explain its working then I can get the idea of its use so far I don't understand the use of functions like dense_rank(). Any idea of what I am missing in my query?

推荐答案

rownum = 3将不起作用，因为rownum已应用于生成的结果集.结果集中的第一行始终为rownum = 1.没有结果集可以匹配rownum = 3，因此您不会返回任何行.

rownum = 3 won't work because rownum is applied to the generated resultset. The first row in a result set always has rownum = 1. No resultset can match rownum = 3 so you get no rows back.

执行此操作的方法是使用rank()，dense_rank()或row_number()之类的分析功能.

The way to do this is with an analytic function like rank(), dense_rank() or row_number().

select * from 
    ( select emp.*
             , dense_rank() over (order by salary desc) rn
      from hr.employees emp
    )
where rn = 3

是否使用rank()，dense_rank()或row_number()取决于您要处理领带的方式.

Whether to use rank(), dense_rank() or row_number() depends on how you want to handle ties.

如果使用row_number()，您将获得表中的第三行，按薪水降序排列.如果您有两个雇员的薪水最高，这是正确的吗?或实际上有四位这样的幸运员工)?

If you use row_number() you will get the third row in the table sorted by salary descending. Would this be correct if you have two employees earning the highest salary? Or indeed four such lucky employees)?

如果使用rank()，如果有联系，子查询将返回排名中的差距，例如1st，2nd =，2nd =，4th，所以您不会获得任何第三名的结果.

If you use rank() the subquery will return gaps in the ranking if there are ties, e.g. 1st, 2nd=, 2nd=, 4th, so you won't get any result for third place.

另一方面，如果有关系，例如，dense_rank()没有空白. 1st，2nd =，2nd =，3rd.这就是为什么我在上面使用它.

On the other hand dense_rank() doesn't have gaps if there are ties, e.g. 1st, 2nd=, 2nd=, 3rd. That is why I used it above.

对于记录，使用rownum的等效查询将需要一个附加的嵌套子查询.

For the record, the equivalent query using rownum would require an additional nested subquery.

select * from (
    select emp.*
           , rownum as rn
    from 
        ( select * from hr.employees
          order by salary desc) emp
    )
where rn = 3

这与row_number()分析解决方案具有相同的结果，即它忽略联系.

This has the same outcome as the row_number() analytic solution, that is it ignores ties.

这篇关于具有所有字段的特定行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！