oracle8i 函数介绍(一):RANK(),DENSE_RANK()
作者: jlandzpa
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
ITPUB 电子杂志 ORACLE8i 函数介绍（一）
HOME: http://www.itpub.net MAIL: [email protected]
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
ITPUB 电子杂志 ORACLE8i 函数介绍（一）
HOME: http://www.itpub.net MAIL: [email protected]
|
