如何找到工资第二高的员工?
是否有任何预定义的函数或方法可用于从员工表中获取第二高的薪水?
Is there any predefined function or method available to get the second highest salary from an employee table?
推荐答案
实现这一点的方法是使用 Oracle 的分析功能.您的特定场景只是我在 另一个线程.
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() 并且有两名员工并列第一个最高工资，则 没有 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.
相关文章