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,
            &nb