Oracle开发之SQL语句案例—分析函数的使用

创建雇员表：

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,
               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,
               dense_rank() over(partition by e.deptno order by sal desc) rankno
          from emp e)
 where rankno <= 3;

注：row_number()排名的名次不会出现重复;rank（）则会出现排名名次相同且如果两人的名次都为2，则下个人的名次则为4而不是为3，而dense_rank（）则与rank()恰好相反。