´´½¨¹ÍÔ±±í£º
create table emp(deptno number(10),ename varchar2(100),sal number(10,2))£»
²åÈëÊý¾Ý
begin
insert into emp values('10','KING',5000);
insert into emp values('10','CLARK',2450);
insert into emp values('10','MILLER',1300);
insert into emp values('20','SCOTT',3000);
insert into emp values('20','FORD',3000);
insert into emp values('20','JONES',2975);
insert into emp values('20','ADAMS',1100);
insert into emp values('20','SMITH',800);
insert into emp values('30','BLAKE',2850);
insert into emp values('30','ALLEN',1600);
insert into emp values('30','TURNER',1500);
insert into emp values('30','WARD',1250);
insert into emp values('30','MARTIN',1250);
insert into emp values('30','JAMES',950);
commit;
end;
1¡¢Ê¹ÓÃrow_number()¡¢rank()¡¢dense_rank()²é³ö¸÷²¿ÃÅнˮ×î¸ßµÄÈý¸öÔ±¹¤ÐÕÃû¡¢Ð½Ë®£¬¶àÓÚÈý¸öµÄֻȡÈý¸ö¡£
select *
from (select e.deptno,
e.ename,
e.sal,
row_number() over(partition by e.deptno order by sal desc) rankno
from emp e)
where rankno <= 3;
select *
from (select e.deptno,
e.ename,
e.sal,
rank() over(partition by e.deptno order by sal desc) rankno
from emp e)
where rankno <= 3;
select *
from (select e.deptno,
e.ename,
&nb