Rank() over的用法，

Rank() over的用法，

oracle创建表：

create table EMPLOYEE
(
  empid  VARCHAR2(50) not null,
  name   VARCHAR2(50),
  salary NUMBER(5),
  sex    VARCHAR2(50),
  dep    VARCHAR2(50)
)
;
comment on table EMPLOYEE
  is '员工表';

prompt Loading EMPLOYEE...
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('1', 'joy', 1500, 'm', '综合部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('2', 'mano', 1800, 'f', '综合部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('3', 'cha', 2000, 'm', '综合部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('4', 'rose', 2100, 'm', '综合部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('5', 'phea', 1400, 'f', '人力资源部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('6', 'arthur', 2100, 'm', '人力资源部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('7', 'sasun', 1000, 'f', '人力资源部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('8', 'jack', 4000, 'm', '开发部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('9', 'maech', 3000, 'm', '开发部');
insert into EMPLOYEE (empid, name, salary, sex, dep)
values ('10', 'gab', 1900, 'm', '人力资源部');
commit;

结果：

1、有一个表Employee，查一个公司里所有超过平均工资的员工。

select m.name, m.salary
  from EMPLOYEE m, (select avg(t.salary) salavg from EMPLOYEE t) n
 where m.salary > n.salavg;

2、女性员工数大于等于2个人的部门。

select n.dep from (select count(*)  numb,t.dep  from EMPLOYEE t where t.sex = 'f'  group by t.dep) n where n.numb>1;

select n.dep
  from (select count(*) numb, t.dep
          from EMPLOYEE t
         where t.sex = 'f'
         group by t.dep) n
 where n.numb > 1;

3、分页语句。

SELECT *
  FROM (SELECT ROWNUM AS rowno, t.*        
          FROM Employee t        
         WHERE 1 = 1
           AND ROWNUM <= 10) table_alias
 WHERE table_alias.rowno >= 5;


SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM (SELECT *
                  FROM Employee) a
         WHERE ROWNUM <= 10)
 WHERE rn >= 5

以下是rank over的用法

1、查公司工资前三的人员

select * from (select rank() over(order by S.salary desc) rk,S.name,S.salary from EMPLOYEE S) T where T.rk<=3;

select *
  from (select rank() over(order by S.salary desc) rk, S.name, S.salary
          from EMPLOYEE S) T
 where T.rk <= 3;

2、对所有的工资进行排序， 整个结果集是一个分组，以name进行排名

SELECT t.name, t.salary, rank() OVER(ORDER BY t.salary) rank
  FROM EMPLOYEE t;

3、以t.dep进行分组，对工资进行排名。

SELECT t.dep, t.name,t.salary,
       rank() OVER(PARTITION by t.dep ORDER BY t.salary) rank
  FROM EMPLOYEE t;

4、以t.sex,t.dep进行分组，在每个组内以b进行排名。

SELECT t.sex,t.dep,t.salary,
       rank() OVER(PARTITION by t.sex, t.dep ORDER BY t.salary) rank
  FROM EMPLOYEE t;

总结：

1、partition  by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。

2、Rank 是在每个分组内部进行排名的。

参考：http://www.cnblogs.com/mycoding/archive/2010/05/29/1747065.html

转载请注明：