???????最近看到的一片帖子，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写法，谢谢。

?

???? 全文完。

?