如何找到薪水第二高的员工? [英] How to find the employee with the second highest salary?

问题描述

是否有任何预定义的函数或方法可用于从雇员表中获取第二高的薪水?

Is there any predefined function or method available to get the second highest salary from an employee table?

推荐答案

执行此操作的方法是使用Oracle的Analytic函数.您的特定情况只是

The way to do this is with Oracle's Analytic functions. Your particular scenario is just a variant on the solution I provided in another thread.

如果您只想选择第二高的薪水，那么DENSE_RANK()，RANK()和ROW_NUMBER()中的任何一个都可以解决问题:

If you are interested in just selecting the second highest salary then any of DENSE_RANK(), RANK() and ROW_NUMBER() will do the trick:

SQL> select * from
  2   ( select sal
  3            , rank() over (order by sal desc) as rnk
  4     from
  5      ( select distinct sal
  6        from emp )
  7    )
  8  where rnk = 2
  9  /

       SAL        RNK
---------- ----------
      3000          2

SQL> 

但是，如果要选择其他信息，例如薪水第二高的员工的姓名，则选择的功能将影响结果.选择一个以上的主要原因是当出现平局时发生的情况.

However, if you want to select additional information, such as the name of the employee with the second highest salary, the function you choose will affect the result. The main reason for choosing one over another is what happens when there is a tie.

如果使用ROW_NUMBER()，它将返回按薪水排序的第二个雇员:如果有两个雇员并列最高工资怎么办?如果有两名雇员并列第二高的工资怎么办?如果您使用RANK()，并且有两名雇员并列第一高薪，则将出现 no 记录，且RANK =2.

If you use ROW_NUMBER() it will return the second employee ordered by salary: what if there are two employees tying for the highest salary? What if there are two employees tying for the second highest salary? Wheareas if you use RANK() and there are two employees tying for first highest salary, there will be no records with RANK = 2.

在这种情况下，我建议DENSE_RANK()通常是最安全的函数，但是它确实取决于特定的业务需求.

I suggest DENSE_RANK() is the usually the safest function to choose in these cases, but it really does depend on the specific business requirement.

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