如何在不使用分析功能的情况下明智地获得第二高薪部门?

问题描述

假设每个部门有3名员工.我们共有3个部门.下面是示例源表

Suppose we have 3 employees in each department.we have total 3 departments . Below is the sample source table

Emp deptno salary
A    10     1000
B    10     2000
C    10     3000
D    20     7000
E    20     9000
F    20     8000
G    30     17000
H    30     15000
I    30     30000

输出

B    10     2000
F    20     8000
G    30     17000

使用解析函数density_rank，我们可以明智地达到第二高的薪水部门.

With using analytic function dense_rank we can achive the second highest salary dept wise.

我们可以不使用任何分析功能来实现这一点吗?

Can we achieve this without using ANY analytic function ???

Max()还是解析函数??

Is Max() is also analytic function ??

推荐答案

这很痛苦，但是您可以做到.以下查询获得第二高的薪水:

It is a pain, but you can do it. The following query gets the second highest salary:

select t.deptno, max(t.salary) as maxs
from table t
where t.salary < (select max(salary)
                  from table t2
                  where t2.deptno = t.deptno
                 )
group by t.deptno;

然后您可以使用它来获取员工:

You can then use this to get the employee:

select t.*
from table t join
     (select t.deptno, max(t.salary) as maxs
      from table t
      where t.salary < (select max(salary)
                        from table t2
                        where t2.deptno = t.deptno
                       )
      group by t.deptno
     ) tt
     on t.deptno = tt.deptno and t.salary = tt.maxs;

这篇关于如何在不使用分析功能的情况下明智地获得第二高薪部门?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！