Rank() over的用法

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

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;

结果：

![20150825091330523][]

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

select m.name, m.salary
      from EMPLOYEE m, (select avg(t.salary) salavg from EMPLOYEE t) n
     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;

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;

![20150825092343889][]

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

![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;

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

![20150825091335143][]

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

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

![20150825091338899][]

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

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

![20150825091342267][]

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;

![20150825091346146][]

**总结：**

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

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

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

转载请注明：

[20150825091330523]: /images/20220731/d442d8746a184c74a507903a6baf06d1.png
[20150825092339102]: /images/20220731/64c52e85120e4333b1066a4d19971d9e.png
[20150825092343889]: /images/20220731/9dc0e3a8e4814a7397b442b2a1178e95.png
[20150825092347408]: https://img-blog.csdn.net/20150825092347408
[20150825092349291]: /images/20220731/51dff54c6c174f32a526bf482fd8c7ea.png
[20150825091335143]: /images/20220731/6929918838dd4d4da56913d473eeac0a.png
[20150825091338899]: /images/20220731/0bb5a695c9af4de78c3103ffca5cd530.png
[20150825091342267]: /images/20220731/b29be93ed6b74def8025f6e3d61e9ea9.png
[20150825091346146]: /images/20220731/d11acb2552604d1c83cac62a0f229a04.png
[http_www.cnblogs.com_mycoding_archive_2010_05_29_1747065.html]: http://www.cnblogs.com/mycoding/archive/2010/05/29/1747065.html