Rank() over的用法

Dear 丶 2022-08-04 11:46 140阅读 0赞

oracle创建表：

  1. create table EMPLOYEE
  2. (
  3.   empid  VARCHAR2(50) not null,
  4.   name   VARCHAR2(50),
  5.   salary NUMBER(5),
  6.   sex    VARCHAR2(50),
  7.   dep    VARCHAR2(50)
  8. )
  9. ;
  10. comment on table EMPLOYEE
  11.   is '员工表';
  13. prompt Loading EMPLOYEE...
  14. insert into EMPLOYEE (empid, name, salary, sex, dep)
  15. values ('1', 'joy', 1500, 'm', '综合部');
  16. insert into EMPLOYEE (empid, name, salary, sex, dep)
  17. values ('2', 'mano', 1800, 'f', '综合部');
  18. insert into EMPLOYEE (empid, name, salary, sex, dep)
  19. values ('3', 'cha', 2000, 'm', '综合部');
  20. insert into EMPLOYEE (empid, name, salary, sex, dep)
  21. values ('4', 'rose', 2100, 'm', '综合部');
  22. insert into EMPLOYEE (empid, name, salary, sex, dep)
  23. values ('5', 'phea', 1400, 'f', '人力资源部');
  24. insert into EMPLOYEE (empid, name, salary, sex, dep)
  25. values ('6', 'arthur', 2100, 'm', '人力资源部');
  26. insert into EMPLOYEE (empid, name, salary, sex, dep)
  27. values ('7', 'sasun', 1000, 'f', '人力资源部');
  28. insert into EMPLOYEE (empid, name, salary, sex, dep)
  29. values ('8', 'jack', 4000, 'm', '开发部');
  30. insert into EMPLOYEE (empid, name, salary, sex, dep)
  31. values ('9', 'maech', 3000, 'm', '开发部');
  32. insert into EMPLOYEE (empid, name, salary, sex, dep)
  33. values ('10', 'gab', 1900, 'm', '人力资源部');
  34. commit;

结果：

20150825091330523

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

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

20150825092339102

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; 

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

20150825092343889

3、分页语句。

  1. SELECT *
  2.   FROM (SELECT ROWNUM AS rowno, t.*        
  3.           FROM Employee t        
  4.          WHERE 1 = 1
  5.            AND ROWNUM <= 10) table_alias
  6.  WHERE table_alias.rowno >= 5;
  9. SELECT *
  10.   FROM (SELECT a.*, ROWNUM rn
  11.           FROM (SELECT *
  12.                   FROM Employee) a
  13.          WHERE ROWNUM <= 10)
  14.  WHERE rn >= 5

20150825092347408 20150825092349291

以下是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;

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

20150825091335143

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

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

20150825091338899

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

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

20150825091342267

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

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

20150825091346146

总结：

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

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

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

转载请注明：

发表评论

表情：
评论列表 （有 0 条评论，140人围观）

还没有评论，来说两句吧...

相关阅读