部门号分组 oracle,[简单]oracle 按部门分组取每部门薪水最大的2条记录

select *

from emp t

where (select count(1) + 1

from emp

where nvl(deptno,0) = nvl(t.deptno,0)

and nvl(sale,0) > nvl(t.sale,0)) <= 2

结果为：

Emp表按部门分组，按薪水排列结果如下：

可以看到原帖的答案没有考虑到有null值情况，考虑空值的sql为：

select *

from emp t

where (select count(1) + 1

from emp

where nvl(deptno,0) = nvl(t.deptno,0)

and nvl(sale,0) > nvl(t.sale,0)) <= 2

order by deptno,sale desc nulls last

结果为：

或者使用分析函数：

select *

from (select emp.*,

dense_rank() over(partition by deptno order by sale desc nulls last) rn

from emp)

where rn <= 2;

检验2个结果是否相同：

select t.empno

from emp t

where (select count(1) + 1

from emp

where nvl(deptno,0) = nvl(t.deptno,0)

and nvl(sale, 0) > nvl(t.sale, 0)) <=2

minus

select t.empno

from (select emp.*,

dense_rank() over(partition by deptno order by sale desc nulls last) rn

from emp) t

where rn <= 2;

select t.empno

from (select emp.*,

dense_rank() over(partition by deptno order by sale desc nulls last) rn

from emp) t

where rn <= 2

minus

select t.empno

from emp t

where (select count(1) + 1

from emp

where nvl(deptno,0) = nvl(t.deptno,0)

and nvl(sale, 0) > nvl(t.sale, 0)) <= 2

欢迎提出更好的sql写法，谢谢。

全文完。