2013年8月30日 星期五

如何在無任何適當ORDER BY欄位情況下,使用ROW_NUMBER函數

ROW_NUMBER()函數將賦予各資料分割結果集中,每一筆資料一個1開始至N遞增的整數值Integer。常應用於產生逐筆序號,以供各項特殊需求使用,語法如下:
ROW_NUMBER()
           OVER ([PARTITION BY ] ORDER BY <order by clause>)
l  <patition by clause>:資料群組(選擇參數)
類似GROUP BY概念,將資料區分割成數個群組,相同群組將一同處理
l  <order by clause>:排序(必要參數
指定資料群組中資料的排序方式

<order by clause>ROW_NUMBER語法之必要參數,當使用此函數時必需提供才可執行,但許多情況下並無任何適當欄位資料可用(或產生非預期之序號),對MSSQL可使用系統時間GETDATE()函數,而ORACLE則可使用SYSDATENULL、常數等三種方法,即可克服此問題。將以四季為範例,即春天(1)、夏天(2)、秋天(3)及冬天(4)依季別產生其對應序號,語法及說明如下

SQL
說明
MSSQL
SELECT *
, ROW_NUMBER() OVER(ORDER BY GETDATE()) Seq--T1
--, ROW_NUMBER() OVER(ORDER BY NULL) Seq2  --T2
--, ROW_NUMBER() OVER(ORDER BY 1) Seq3     --T3
FROM   
  (VALUES ('Spring', '春天')
         , ('Summer', '夏天')
         , ('Autumn', '秋天')
         , ('Winter', '冬天')
   ) AS Seasons(Season, Disp)
ü  T1GETDATE()函數-可用
ü  T2: NULL-不可用
Windowed functions do not support constants as ORDER BY clause expressions.
ü  T31(常數)-不可用
Windowed functions do not support integer indices as ORDER BY clause expressions.
ORACLE
SELECT A.*
   , ROW_NUMBER() OVER(ORDER BY SYSDATE) Seq 
   , ROW_NUMBER() OVER(ORDER BY NULL)     Seq2
   , ROW_NUMBER() OVER(ORDER BY 1)        Seq3      
FROM
     (
      SELECT 'Spring' Season, '春天' Disp
      FROM DUAL
      UNION ALL
      SELECT 'Summer', '夏天'
      FROM DUAL
      UNION ALL
      SELECT 'Autumn', '秋天'
      FROM DUAL
      UNION ALL
      SELECT 'Winter', '冬天'
      FROM DUAL
     ) A
SYSDATE函數、NULL1(常數)均可使用。

執行結果:

沒有留言:

張貼留言