rank() function in application

rank() function in application

 

场景：使用oracle scott/tiger 登录 使用emp表
     1）取出各部门工资小于各部门平均工资80%的员工
     2）取出各部门工资排名第二名的员工

 

rank() 函数返回结果集分区内指定字段的值的排名，指定字段的值的排名是相关行之前的排名加一。
　　语法：
　　RANK() OVER([<partiton_by_clause>]<order by clause>)
　　partition_by_clause 将from子句生成的结果集划分为应用到RANK函数的分区。
　　Order_by_clause确定将RANK值应用到分区中的行时所使用的顺序。

 

表结构：

-- Create table
create table EMP
(
  EMPNO    NUMBER(4) not null,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table EMP
  add constraint PK_EMP primary key (EMPNO)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table EMP
  add constraint FK_DEPTNO foreign key (DEPTNO)
  references DEPT (DEPTNO);

 

抽取emp数据：

prompt PL/SQL Developer import file
prompt Created on 2011年5月27日星期五 by David
set feedback off
set define off
prompt Disabling foreign key constraints for EMP...
alter table EMP disable constraint FK_DEPTNO;
prompt Loading EMP...
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);
commit;
prompt 14 records loaded
prompt Enabling foreign key constraints for EMP...
alter table EMP enable constraint FK_DEPTNO;
set feedback on
set define on
prompt Done.

 

解决方案 SQL:
1)

select a.deptno,a.ename,a.sal,b.avgsal
  from emp a,
       (select t.deptno, avg(t.sal) * 0.8 as avgSal
          from emp t
         group by t.deptno) b
 where a.deptno = b.deptno
   and a.sal < b.avgSal
 order by a.deptno;

 或者：

select a.deptno,a.ename,a.sal,a.avgsal  
  from (select t.*, avg(t.sal) over(partition by t.deptno) * 0.8 as avgSal
          from emp t) a
 where sal < avgsal

 

结果：
1 10 MILLER   1300.00   2333.33333333333
2 20 SMITH     800.00    1740
3 20 ADAMS   1100.00   1740
4 30 WARD    1250.00   1253.33333333333
5 30 MARTIN  1250.00   1253.33333333333
6 30 JAMES    950.00     1253.33333333333

 

2)

select b.deptno,b.ename,b.sal
  from (select a.*,
               rank() over(partition by a.deptno order by a.sal desc) ra
          from emp a) b
 where b.ra = 2;

 

结果：
1 10 CLARK 2450.00
2 30 ALLEN 1600.00

 

其中部门号为20的部门工资中有两个并列第一，所以没有第二名。

 

评论

1 楼 weiruan85 2011-05-27  

thanks for your sharing