最近看到的一片文章，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寫法，謝謝。
全文完。