oracle中利用函数获得topn的结果集.doc

oracle中利用函数获得top n的结果集 RANK(),DENSE_RANK()是oracle8i 的两个有趣的分析函数, 可以用来解决top-N问题.两个函数的区别可以从下面例子中看出：create table emp(Empno varchar2(10),Ename varchar2(10),Job varchar2(10),Mgr varchar2(10),Sal varchar2(10);insert into emp values(Empno1,Ename1,Job1,Mgr1,1);insert into emp values(Empno2,Ename2,Job2,Mgr2,2);insert into emp values(Empno3,Ename3,Job3,Mgr3,3);insert into emp values(Empno4,Ename4,Job4,Mgr4,4);insert into emp values(Empno5,Ename5,Job5,Mgr5,5);insert into emp values(Empno6,Ename6,Job6,Mgr6,6);insert into emp values(Empno7,Ename7,Job7,Mgr7,4);insert into emp values(Empno8,Ename8,Job8,Mgr7,5);insert into emp values(Empno9,Ename9,Job9,Mgr7,6);commit;SELECT Empno, Ename, Job, Mgr, Sal,RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS Rank,DENSE_RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS DrankFROM EmpORDER BY SAL Desc NULLS LAST;EMPNO ENAME JOB MGR SAL RANK DRANK- - - - - - -Empno6 Ename6 Job6 Mgr6 6 1 1Empno9 Ename9 Job9 Mgr7 6 1 1Empno5 Ename5 Job5 Mgr5 5 3 2Empno8 Ename8 Job8 Mgr7 5 3 2Empno4 Ename4 Job4 Mgr4 4 5 3Empno7 Ename7 Job7 Mgr7 4 5 3Empno3 Ename3 Job3 Mgr3 3 7 4Empno2 Ename2 Job2 Mgr2 2 8 5Empno1 Ename1 Job1 Mgr1 1 9 6结果是不是很奇妙？如果我们自己写sql 实现，是需要一些技巧的。1.利用RANK()可以实现top-N 问题下面这个sql 可以显示,最大的几个Sal,相同Sal 的记录也同样显示.SELECT Empno, Ename, Job, Mgr, SalFROM(SELECT Empno, Ename, Job, Mgr, Sal,RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS Emp_RankFROM EmpORDER BY SAL Desc NULLS LAST)WHERE Emp_Rank = 1;EMPNO ENAME JOB MGR SAL- - - - -Empno6 Ename6 Job6 Mgr6 6Empno9 Ename9 Job9 Mgr7 6SELECT Empno, Ename, Job, Mgr, SalFROM(SELECT Empno, Ename, Job, Mgr, Sal,RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS Emp_RankFROM EmpORDER BY SAL Desc NULLS LAST)WHERE Emp_Rank 4;EMPNO ENAME JOB MGR SAL- - - - -Empno6 Ename6 Job6 Mgr6 6Empno9 Ename9 Job9 Mgr7 6Empno5 Ename5 Job5 Mgr5 5Empno8 Ename8 Job8 Mgr7 5SELECT Empno, Ename, Job, Mgr, SalFROM(SELECT Empno, Ename, Job, Mgr, Sal,RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS Emp_RankFROM EmpORDER BY SAL Desc NULLS LAST)WHERE Emp_