sql分組查詢語句--行內分組(非聚合分組)

langgufu314發表於2013-01-18

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根據COL1分組,在分組內部根據 COL2排序,而此函式計算的值就表示每組內部排序後的順序編號(組內連續的唯一的)

SQL> SELECT G.*, ROW_NUMBER() OVER(PARTITION BY a ,b ORDER BY c DESC) ROWN
2    from (select '1' a, '2' b, '1' c
3            from dual
4          union all
5          select '1', '2', '2'
6            from dual
7          union all
8          select '1', '3', '3'
9            from dual
10          union all
11          select '1', '3', '4'
12            from dual
13          union all
14          select '1', '4', '5' from dual
15          union all
16          select '1','3','5'from dual) G
17 ;

A B C       ROWN
- - - ----------
1 2 2          1
1 2 1          2
1 3 5          1
1 3 4          2
1 3 3          3
1 4 5          1

6 rows selected

 與rownum的區別在於:使用rownum進行排序的時候是先對結果集加入偽列rownum然後再進行排序,而此函式在包含排序從句後是先排序再計算行號碼.

  row_number()和rownum差不多,功能更強一點(可以在各個分組內從1開時排序).

  rank()是跳躍排序,有兩個第二名時接下來就是第四名(同樣是在各個分組內).

  dense_rank()l是連續排序,有兩個第二名時仍然跟著第三名。相比之下row_number是沒有重複值的 .

  lag(arg1,arg2,arg3): 
arg1是從其他行返回的表示式 
arg2是希望檢索的當前行分割槽的偏移量。是一個正的偏移量,時一個往回檢索以前的行的數目。 
arg3是在arg2表示的數目超出了分組的範圍時返回的值。

語句一:

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

相關文章