dense_rank()，rank(),row_number()的输出差异

最新推荐文章于 2024-10-22 10:46:08 发布

转载最新推荐文章于 2024-10-22 10:46:08 发布 · 124 阅读

本文通过具体实例介绍了SQL中三种不同的排名函数：DENSE_RANK(), RANK() 和 ROW_NUMBER() 的使用方法，并展示了如何从每个部门选取最高薪资的前四名员工。

SQL> select * from employee;

        ID     DEPTID NAME                     SALARY
---------- ---------- -------------------- ----------
         5          2 e                           400
         6          2 f                           200
         1          1 a                           100
         2          1 b                           200
         3          2 c                           300
         4          2 d                           300

6 rows selected.

Elapsed: 00:00:00.01
SQL> select *
2    from (select e1.deptid,
3                 e1.name,
4                 e1.salary,
5                 dense_rank() over(partition by e1.deptid order by e1.salary desc) rn
6            from employee e1)
7   where rn <= 4;

    DEPTID NAME                     SALARY         RN
---------- -------------------- ---------- ----------
         1 b                           200          1
         1 a                           100          2
         2 e                           400          1
         2 c                           300          2
         2 d                           300          2
         2 f                           200          3

6 rows selected.

Elapsed: 00:00:00.00
SQL> edit
Wrote file afiedt.buf

1 select *
2    from (select e1.deptid,
3                 e1.name,
4                 e1.salary,
5                 rank() over(partition by e1.deptid order by e1.salary desc) rn
6            from employee e1)
7* where rn <= 4
SQL> /

    DEPTID NAME                     SALARY         RN
---------- -------------------- ---------- ----------
         1 b                           200          1
         1 a                           100          2
         2 e                           400          1
         2 c                           300          2
         2 d                           300          2
         2 f                           200          4

6 rows selected.

Elapsed: 00:00:00.03
SQL> edit
Wrote file afiedt.buf

1 select *
2    from (select e1.deptid,
3                 e1.name,
4                 e1.salary,
5                 row_number() over(partition by e1.deptid order by e1.salary desc) rn
6            from employee e1)
7* where rn <= 4
SQL> /

    DEPTID NAME                     SALARY         RN
---------- -------------------- ---------- ----------
         1 b                           200          1
         1 a                           100          2
         2 e                           400          1
         2 c                           300          2
         2 d                           300          3
         2 f                           200          4

6 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-673847/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-673847/