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的部门工资中有两个并列第一,所以没有第二名。