• Oracle 中 rownum、row_number()、rank()、dense_rank() 函数的用法


    Ø  简介

    在之前还以为在 Oracle 中只能使用 rownum 这个伪列来实现分页,其实不然。在 Oracle 也与 MSSQL 一样,同样支持 row_number 函数,以及和 rankdense_rank 这两个函数。下面就来讨论 rownum row_number 函数的区别,以及另外两个函数的使用。

     

    1.   rownum

    rownum Oracle 在查询时对结果集输出的一个伪列,这个列并不是真实存在的,当我们进行每一个 SELECT 查询时,Oracle 会帮我们自动生成这个序列号(rownum),该序列号是顺序递增的,用于标识行号。通常可以借助 rownum 来实现分页,下面来看具体实现,比如我们需要取 emp 表中46行的记录:

    1)   首先,我们来看一个奇怪的现象

    SELECT * FROM emp WHERE rownum >= 4 AND rownum <= 6;

    啪,一执行,呀,怎么没数据啊?这并不是我们写错了,要解释这个问题,我们先来看一个图,就明白其中原由了。


    clip_image002

    由图可以看出,当我们取出第一条记录时,此时(rownum = 1) >= 3不成立,所以该记录会被排除;然后再取第二条,此时任然 rownum = 1,因为只有成功满足一条记录,rownum 才会加1,所以不满足又被排除掉了。这样依次类推,最终都不满足条件,所以全部都被排除掉了。所以,以下语句始终查不出数据:

    SELECT * FROM emp WHERE rownum > 1;

     

    然后,在看另外一边(就是接下来用的这种判断方式),首先取第一条(满足),第二条也满足,直到(rownum = 7) <= 6,所以会取出6条记录,此时 rownum 的值为1,2,3,4,5,6。好了,搞清楚原理后我们就来实现。

     

    2)   根据对 rownum 的分析,便改为以下语句

    SELECT rownum, t1.* FROM (

      SELECT rownum rnum, t1.* FROM emp t1 WHERE rownum <= 6

    ) t1 WHERE t1.rnum >= 4;

    clip_image004

    这样,通过子查询,先取出前6行,再过滤掉前3行,就得到了我们需要的数据。注意:之前提过,每个 SELECT 都会产生一个 rownum 序列号,所有上面会可以输出两个 rownum 序列号,dual 也不例外

    SELECT t1.*, rownum FROM dual t1;

    clip_image005

     

    3)   除了使用以上语句,我们还可以这样写

    SELECT rownum, t1.* FROM (

      SELECT rownum rnum, t1.* FROM emp t1

    ) t1 WHERE t1.rnum >= 4 AND rnum <= 6; --或使用 BETWEEN 子句

    clip_image007

    同样,可以完成以上功能。但分析一下,这种方式视乎没有上面的方式效率高,因为,这里是先查出所有(先将 rownum 分配好)数据,再进行第二次 rownum 过滤。

     

    4)   有时候,我们还需要通过排序后再分页,该怎么实现呢?

    使用排序并分页,也需要注意以下问题。

    首先,我们来看下排序的全部数据:

    SELECT * FROM emp ORDER BY sal;

    clip_image009

    按照上面的要求,我们应该是取出 empno(7521,7654,7934) 的员工,OK

     

    不是说用第一种方式,效率很高么?那就来使用它实现,更改的后的 SQL

    SELECT * FROM (

      SELECT rownum rnum, t1.* FROM emp t1 WHERE rownum <= 6 ORDER BY sal

    ) WHERE rnum >= 4;

    clip_image011

    结果是不是又纳闷了?怎么76987566也出来,而且还不是按我们预想的排序的!

     

    好,我们再做个假设,以上语法是不是先查询出结果后,再将结果集过滤和排序的呢?为了验证这个疑点,很简单我们做以下测试:

    SELECT * FROM (

      SELECT * FROM (SELECT rownum rnum, t1.* FROM emp t1)

      WHERE rownum <= 6 ORDER BY sal

    ) WHERE rnum >= 4;

    clip_image013

    结果与前面的推断是一样的,就是先查询出结果(产生的 rownum 是没有经过排序的),再排序,最后分页(过滤)。我们看一下未排序的原始数据:

    SELECT rownum, t1.* FROM emp t1;

    clip_image015

    所以,我们得出一个结论:当我们同时过滤 rownum 和排序时,是先按默认的排序生成 rownum 后,再进行排序和过滤的

     

    5)   其实上面的排序和分页,并不是准确有效的。因为我们需要的是,rownum 的顺序是根据我们指定的排序产生的,这样再进行分页才是准确的。所以正确的排序和分页应该这样写:

    SELECT * FROM (

      SELECT rownum rnum, t1.* FROM (SELECT * FROM emp ORDER BY sal) t1

    ) WHERE rnum BETWEEN 4 AND 6;

    clip_image017

    执行步骤:先根据指定的字段排序;再产生 rownum 序列号;最后进行分页。

     

    2.   row_number() 函数

    在前面使用 rownum 实现分页,虽然是可以实现的,但是看似是否有点别扭。因为当需要对分页排序时,rownum 总是先生成序列号再排序,其实这不时我们想要的。 row_number() 函数则是先排序,再生成序列号。这也是 row_number rownum 主要的区别。下面来看 row_number() 的使用:

    n  语法:

    row_number() over([partition by col1] order by col2 [ASC | DESC] [,col3 [ASC | DESC]]...)

    参数解释:

    row_number() over(): 是固定写法,即不能单独使用 row_nubmer() 函数;

    partition by: 可选的。用于指定分组(或分开依据)的列,类似 SELECT 中的 group by 子句;

    order by: 用于指定排序的列,类似 SELECT 中的 order by 子句。

     

    1.   基本用法

    SELECT row_number() over(order by empno) AS rnum, t1.* FROM emp t1;

    clip_image019

     

    2.   使用 row_number() 分页

    SELECT * FROM (

      SELECT row_number() over(order by empno) AS rnum, t1.* FROM emp t1

    ) t WHERE t.rnum BETWEEN 4 AND 6;

    clip_image021

    是不是看上去,比使用 rownum 优雅了许多。

     

    3.   使用 partition by 参数分区生成序号

    当使用 partition by 参数时,序号将可能不是唯一的,因为序号的生成只会在当前分区中唯一,下一个分区又将从1开始计算,例如:

    SELECT row_number() over(partition by deptno order by empno) AS rnum, t1.* FROM emp t1;

    clip_image023

     

    3.   rank() dense_rank() 函数

    rank() row_number() 的区别在于,rank() 会按照排序值相同的为一个序号(以下称为:),第二个不同排序值将显示所有行的递增值,而不是当前序号加1。看示例:

    SELECT rank() over(order by job) rnum, job, ename FROM emp t1;

    clip_image024

     

    dense_rank() 函数,与 rank() 区别在于,第二个不同排序值,是对当前序号值加1,看示例:

    SELECT dense_rank() over(order by job) rnum, job, ename FROM emp t1;

    clip_image025

     

    当指定 partition by 参数时,将根据指定的字段分组,进行分组计算序号值,序号值只在当前分组中有效,例如:

    SELECT rank() over(partition by deptno order by job) rnum, job, ename, deptno FROM emp t1;

    clip_image026

     

    SELECT dense_rank() over(partition by deptno order by job) rnum, job, ename, deptno FROM emp t1;

    clip_image027

     

    4.   over() 函数结合聚合函数的使用

    SELECT empno, ename, sal, hiredate, COUNT(sal) OVER(ORDER BY hiredate DESC) count FROM emp;

    clip_image028

     

    SELECT empno, ename, sal, hiredate, MAX(sal) OVER(ORDER BY hiredate ASC) max FROM emp;

    clip_image029

     

    SELECT empno, ename, sal, hiredate, MIN(sal) OVER(ORDER BY hiredate DESC) min FROM emp;

    clip_image030

     

    SELECT empno, ename, sal, hiredate, AVG(sal) OVER(ORDER BY hiredate DESC) avg FROM emp;

    clip_image031

     

    SELECT empno, ename, sal, hiredate, SUM(sal) OVER(ORDER BY hiredate DESC) sum FROM emp;

    clip_image032

     

    5.   综合案例

    1)   查询前 100 条记录

    SELECT * FROM emp WHERE rownum <= 100;

    注意:如果以上语句需要排序后再筛选,并不是能使用 rownum 实现,而需要使用 row_number() 函数。

     

    2)   查出 4 ~ 6 条的记录,并按员工编号排序(分页运用)

    SELECT * FROM (SELECT row_number() over(order by empno) rnum, t.* FROM emp t) t

    WHERE t.rnum >= 4 AND t.rnum <= 6;

    clip_image034

     

    3)   查出每个部门工资最高的员工

    SELECT * FROM (SELECT row_number() over(partition by deptno order by sal DESC) rnum, t.* FROM emp t) t WHERE t.rnum = 1;

    clip_image036

     

    4)   查出每个部门工资最高的所有员工(排名并列的)

    SELECT * FROM (SELECT rank() over(partition by deptno order by sal DESC) rnum, t.* FROM emp t) t WHERE t.rnum = 1;

    clip_image038

     

    5)   查出每个部门工资排名第三的所有员工(排名并列的)

    SELECT * FROM (SELECT dense_rank() over(partition by deptno order by sal ASC) rnum, t.* FROM emp t) t WHERE t.rnum = 3;

    clip_image040

     

    注意:如果使用 rank() 是不行的,因为20号部门并列第二的员工有2个,序号3就被跳掉了,直接跳到了序号4,使用以下语句可以查看到:

    SELECT rank() over(partition by deptno order by sal ASC) rnum, t.* FROM emp t;

    clip_image042

    所以,使用 rank() 将会得到错误的结果:

    SELECT * FROM (SELECT rank() over(partition by deptno order by sal ASC) rnum, t.* FROM emp t) t WHERE t.rnum = 3;

    clip_image044

     

    Ø  总结

    好了,关于排序函数就讨论到这里了,感觉有点难记住它们的区别。所以可以结合上面的案例去记忆:

    1.   如果需要取前多少条记录,就使用 rownum 伪列。rownum 就类似于 SQL Server TOP 子句的用法,但是 rownum 不能用于排序并过滤的场合。

     

    2.   如果取多少条到多少条的记录(分页),就是使用 row_number() 函数。

    例如:查出 4 ~ 6 条的记录,并按员工编号排序。

     

    3.   如果取某个组别中最大值记录或最小值的记录,也可以使用 row_number() 函数,并结合 partition by 参数。

    例如:查出每个部门工资最高的员工。

     

    4.   如果取某个组别中并列最大值或最小值得记录,就使用 rank() 函数,并结合 partition by 参数。

    例如:查出每个部门工资最高的所有员工。

     

    5.   如果取某个组别中并列排名几记录,就使用 dense_rank() 函数,并结合 partition by 参数。

    例如:查出每个部门工资排名第三的所有员工。

     

    当然,以上只是举例,还有更多的用法需要我们去举一反三。

  • 相关阅读:
    iOS面试题总结整理(附答案)
    iOS App上传项目遇到的问题
    Could not find Developer Disk Image
    xcode下载方式
    iOS App上架流程(2016详细版)
    Xcode中的iOS模拟器(iOS Simulator)的介绍和使用心得
    iOS开发之17个常用代码整理
    iOS求职之OC面试题
    Android xmpp 连接基本方法
    Ubuntu安装过程
  • 原文地址:https://www.cnblogs.com/abeam/p/12153362.html
Copyright © 2020-2023  润新知