`
liyuandong
  • 浏览: 332151 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

Oracle row_number() over函数的使用(转)

阅读更多
        row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的).

  与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码.

  row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开时排序).

  rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内).

  dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 .

  lag(arg1,arg2,arg3):
arg1是从其他行返回的表达式
arg2是希望检索的当前行分区的偏移量。是一个正的偏移量,时一个往回检索以前的行的数目。
arg3是在arg2表示的数目超出了分组的范围时返回的值。

看几个SQL语句:

语句一:

select row_number() over(order by sale/cnt desc) as sort, sale/cnt
from (
select -60 as sale,3 as cnt from dual union
select 24 as sale,6 as cnt from dual union
select 50 as sale,5 as cnt from dual union
select -20 as sale,2 as cnt from dual union
select 40 as sale,8 as cnt from dual);

执行结果:

          SORT       SALE/CNT
---------- ----------
             1             10
             2              5
             3              4
             4            -10
             5            -20

 

语句二:查询员工的工资,按部门排序

select ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order from scott.emp;

执行结果:

ENAME                           SAL      SAL_ORDER
-------------------- ---------- ----------
KING                           5000              1
CLARK                          2450              2
MILLER                         1300              3
SCOTT                          3000              1
FORD                           3000              2
JONES                          2975              3
ADAMS                          1100              4
SMITH                           800              5
BLAKE                          2850              1
ALLEN                          1600              2
TURNER                         1500              3
WARD                           1250              4
MARTIN                         1250              5
JAMES                           950              6

已选择14行。

语句三:查询每个部门的最高工资

select deptno,ename,sal from
     (select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order
         from scott.emp) where sal_order <2;

执行结果:

       DEPTNO ENAME                          SAL
---------- -------------------- ----------
           10 KING                          5000
           20 SCOTT                         3000
           30 BLAKE                         2850

已选择3行。

语句四:

select deptno,sal,rank() over (partition by deptno order by sal) as rank_order from scott.emp order by deptno;

执行结果:

     DEPTNO         SAL RANK_ORDER
---------- ---------- ----------
         10        1300           1
         10        2450           2
         10        5000           3
         20         800           1
         20        1100           2
         20        2975           3
         20        3000           4
         20        3000           4
         30         950           1
         30        1250           2
         30        1250           2
         30        1500           4
         30        1600           5
         30        2850           6

已选择14行。

语句五:

select deptno,sal,dense_rank() over(partition by deptno order by sal) as dense_rank_order from scott.emp order by deptn;

执行结果:

     DEPTNO         SAL DENSE_RANK_ORDER
---------- ---------- ----------------
         10        1300                 1
         10        2450                 2
         10        5000                 3
         20         800                 1
         20        1100                 2
         20        2975                 3
         20        3000                 4
         20        3000                 4
         30         950                 1
         30        1250                 2
         30        1250                 2
         30        1500                 3
         30        1600                 4
         30        2850                 5

已选择14行。

语句六:

select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) as lag_ from scott.emp order by deptno;

执行结果:

 

 

     DEPTNO ENAME                        SAL LAG_
---------- -------------------- ---------- --------------------
         10 CLARK                       2450
         10 KING                        5000 CLARK
         10 MILLER                      1300 KING
         20 ADAMS                       1100
         20 FORD                        3000 ADAMS
         20 JONES                       2975 FORD
         20 SCOTT                       3000 JONES
         20 SMITH                        800 SCOTT
         30 ALLEN                       1600
         30 BLAKE                       2850 ALLEN
         30 JAMES                        950 BLAKE
         30 MARTIN                      1250 JAMES
         30 TURNER                      1500 MARTIN
         30 WARD                        1250 TURNER

已选择14行。

分享到:
评论

相关推荐

    Oracle row_number()over

    Oracle 中的 row_number()over 函数是一个分析函数,用于生成一个连续的数字序列,可以根据某个列或多个列对数据进行分区和排序,然后生成一个唯一的序号。 row_number()over 函数的基本语法为: ``` row_number()...

    oracle分析函数row_number() over()使用

    Oracle的分析函数`ROW_NUMBER() OVER()`是一种强大的工具,用于在查询结果集中为每一行分配一个唯一的序列号。这个函数通常用于数据分页、排名或者为特定条件的记录分配顺序。下面我们将详细讨论`ROW_NUMBER() OVER...

    oracle row_number用法

    ### Oracle Row_Number 函数详解 #### 一、Row_Number 基础概念 在Oracle数据库中,`ROW_NUMBER()`函数是一种窗口函数,主要用于为查询结果中的每一行分配一个唯一的序列号。这一特性使得它在处理分组数据时非常...

    oracle的row_numer()函数的使用

    介绍了 row_number() over(order by column asc) 函数和 row_number() over(partition by column1 order by column2 asc) 的使用实例和方法

    row_number,根据多个字段过滤,partition by

    使用`ROW_NUMBER()`函数与`PARTITION BY`子句 为了实现上述需求,可以采用SQL中的`ROW_NUMBER()`窗口函数配合`PARTITION BY`子句来完成。下面详细介绍这一过程: #### 2.1 `ROW_NUMBER()`函数概述 `ROW_NUMBER()...

    深入探讨:oracle中row_number() over()分析函数用法

    Oracle数据库中的`row_number() over()`分析函数是一个非常实用的工具,用于为数据集中的每一行分配一个唯一的整数,这个数字基于指定的排序条件。在处理大数据集时,它可以帮助我们进行分页、排名或者在复杂的查询...

    Oracle row_number() over()解析函数高效实现分页

    Oracle数据库中的`row_number() over()`函数是一种窗口函数,它在处理数据分页时非常有用。这个函数可以为查询结果集中的每一行分配一个唯一的行号,这些行号基于一个指定的排序规则。在此场景中,我们将探讨如何...

    RANK OVER()和ROW_NUMBER()OVER的使用

    介绍rank() over()分析函数和 row_number() over 的使用实例及其异同点

    oracle中rownum和row_number()

    row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。 与rownum的区别在于:使用rownum进行排序的...

    Oracle开发之分析函数(Rank, Dense_rank, row_number)

    Oracle分析函数Rank、Dense_rank和row_number是用于处理数据集的高级工具,它们在数据库查询中发挥着关键作用,特别是在需要对数据进行排序和分组时。这三种函数都有各自的特点,适用于不同的业务场景。 1. **row_...

    ORACLE_OVER函数

    OVER函数通常与窗口函数(如RANK(), DENSE_RANK(), ROW_NUMBER(), SUM(), AVG()等)一起使用。其基本语法形式如下: ```sql &lt;window_function&gt; OVER ( [window_spec] ) ``` 其中`&lt;window_function&gt;`代表具体的窗口...

    Oracle数据库rownum和row_number的不同点

    相比之下,`ROW_NUMBER()` 是一种窗口函数(分析函数),它在SQL标准中定义,Oracle 10g及以上版本开始支持。`ROW_NUMBER()` 可以根据指定的排序规则为每一行分配一个唯一的序号,同样从1开始。与 `ROWNUM` 不同,`...

    使用row_number()实现分页实例

    内部查询使用`ROW_NUMBER()`函数,按照`Id`字段对`News`表中的记录进行排序,并为每行生成一个行号`[$Row_Num]`。`OVER`子句定义了排序的顺序,这里是根据`Id`升序排列。 外部查询从临时表 `_temp` 中选择行,只...

    Oracle_详解分析函数

    2. **Top/Bottom N查询**:使用`ROW_NUMBER()`或`RANK()`函数结合`WHERE`子句实现。 3. **First/Last排名查询**:使用`FIRST_VALUE`或`LAST_VALUE`函数获取第一行或最后一行的值。 4. **按层次查询**:通过嵌套等级...

    ORACLE 常用分析函数

    分析函数2(Rank, Dense_rank, row_number);分析函数3(Top/Bottom N、First/Last、NTile);窗口函数;报表函数;分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述  ROW_NUMBER () OVER([partition_clause]...

    各数据库分页语法支持

    Oracle 支持多种分页方式,包括三层嵌套分页、ROW_NUMBER() 函数分页及 ROWNUM 控制最大条数的方法。 1. **三层嵌套分页**: ```sql SELECT * FROM ( SELECT row_.*, ROWNUM as rownum FROM ( SELECT sid ...

    oracle分析函数

    ### Oracle分析函数详解:row_number() over 在Oracle数据库中,分析函数(Analytic Functions)是一种强大而灵活的数据处理工具,它们能够在查询结果集中对每一行数据进行计算,而不像传统的聚合函数那样只返回...

Global site tag (gtag.js) - Google Analytics