oracle中利用函数获得top n的结果集

mengxuyang

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 Drank
FROM Emp
ORDER BY SAL Desc NULLS LAST;

EMPNO ENAME JOB MGR SAL RANK DRANK
---------- ---------- ---------- ---------- ---------- ---------- ----------
Empno6 Ename6 Job6 Mgr6 6 1 1
Empno9 Ename9 Job9 Mgr7 6 1 1
Empno5 Ename5 Job5 Mgr5 5 3 2
Empno8 Ename8 Job8 Mgr7 5 3 2
Empno4 Ename4 Job4 Mgr4 4 5 3
Empno7 Ename7 Job7 Mgr7 4 5 3
Empno3 Ename3 Job3 Mgr3 3 7 4
Empno2 Ename2 Job2 Mgr2 2 8 5
Empno1 Ename1 Job1 Mgr1 1 9 6

结果是不是很奇妙？如果我们自己写sql 实现，是需要一些技巧的。

1.利用RANK()可以实现top-N 问题

下面这个sql 可以显示,最大的几个Sal,相同Sal 的记录也同样显示.

SELECT Empno, Ename, Job, Mgr, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Sal,
RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank = 1;

EMPNO ENAME JOB MGR SAL
---------- ---------- ---------- ---------- ----------
Empno6 Ename6 Job6 Mgr6 6
Empno9 Ename9 Job9 Mgr7 6

SELECT Empno, Ename, Job, Mgr, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Sal,
RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank < 4;

EMPNO ENAME JOB MGR SAL
---------- ---------- ---------- ---------- ----------
Empno6 Ename6 Job6 Mgr6 6
Empno9 Ename9 Job9 Mgr7 6
Empno5 Ename5 Job5 Mgr5 5
Empno8 Ename8 Job8 Mgr7 5

SELECT Empno, Ename, Job, Mgr, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Sal,
RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank < 3;

EMPNO ENAME JOB MGR SAL
---------- ---------- ---------- ---------- ----------
Empno6 Ename6 Job6 Mgr6 6
Empno9 Ename9 Job9 Mgr7 6

2.利用RANK()可以实现top(n,m)问题.

SELECT Empno, Ename, Job, Mgr, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Sal,
RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank > 3 and Emp_Rank < 6;

EMPNO ENAME JOB MGR SAL
---------- ---------- ---------- ---------- ----------
Empno4 Ename4 Job4 Mgr4 4
Empno7 Ename7 Job7 Mgr7 4

linfei

值得研究，谢谢楼主提供