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的部门工资中有两个并列第一,所以没有第二名。
0票
开心
0票
板砖
0票
感动
0票
有用
0票
疑问
0票
难过
0票
无聊
0票
震惊
顶
踩