当前位置:首页 > 开发 > 数据库 > 正文

rank() function in application

发表于: 2011-05-27   作者:crabdave   来源:转载   浏览次数:
摘要: rank() function in application   场景:使用oracle scott/tiger 登录 使用emp表     1)取出各部门工资小于各部门平均工资80%的员工     2)取出各部门工资排名第二名的员工   rank() 函数返回结果集分区内指定字段的值的排名

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

 

rank() function in application

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
三者主要区别在于对相同序号后的下一行记录的处理。 1:RANK():跳跃排序,如果有两个第二名,接下来
原来表数据 现在需求是 每位userinfoid发表的最新一条数据 ( select smallblog. * ,rank() over (pa
create table B ( 店铺 VARCHAR2(100), 销售数量 VARCHAR2(100), 价格 VARCHAR2(100), 日期 DATE )
learning to rank学习 转: http://blog.csdn.net/xuqianghit/article/details/8947819 1. 什么是le
RANK()既是一个聚合函数,也是一个分析函数 其具体的语法如下: 聚合函数语法: 分析函数的语法: RA
1.Delicious.com 热门书签排行榜 按照"过去60分钟内被收藏的次数"进行排名 优点:简单 缺点:排名变
有的时候会遇到这样的问题,我们需要查询一张表,而且要按照业务排序,比如我需要如下的结果: 地区
Kemaswill Learning to Rank 简介 去年实习时,因为项目需要,接触了一下Learning to Rank(以下简称
去年实习时,因为项目需要,接触了一下Learning to Rank(以下简称L2R),感觉很有意思,也有很大的应
去年实习时,因为项目需要,接触了一下Learning to Rank(以下简称L2R),感觉很有意思,也有很大的应
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号