最近看到的一片帖子，http://www.itpub.net/forum.php?mod=viewthread&tid=1865269，按部门分组取薪水最大的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写法，谢谢。
全文完。