iT邦幫忙

2018 iT 邦幫忙鐵人賽
DAY 4
0
Data Technology

SQL Server 學習日誌系列 第 4

04.ROW_NUMBER 次序函數與使用

2020.7.9 不知道什麼原因,發現在這邊的文章圖片遺失了,還好個人部落格有備份,若您要看完整的文章:
序列函數 ROW_NUMBER

前言

會認識這個函數主要是因為要做分群,透過舊有的 Group By 方法會有些 Select 欄位的問題。看過同事的 Stored Procedure 後意外發現透過 ROW_NUMBER PARTITION BY 方式能達到類似的效果,且讓程式有更多邏輯上的應用,相當實用。


介紹

ROW_NUMBER 為顯示分割資料內的資料列的序號,每個分割資料的第一個資料序號從 1 開始。我們透過 PARTITION BY 進行資料分割,其詳細用法如下:

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY expression , .... ] ORDER_BY_CLAUSE ) 

我們以 Study4 相簿資料表為例,透過 Id 排序並回傳資料列序號

SELECT ROW_NUMBER() OVER( ORDER BY Id DESC) AS row,
       Id,
       [Type],
       Title,
       [Description]
  FROM [dbo].[Album]

https://d2mxuefqeaa7sj.cloudfront.net/s_294B82E52F131051DC54044171C3EE7DF0768263E9119A2BDA42103C54C6FFEF_1513953634274_image.png

我們以 Id 進行分割資料表,來看看有什麼結果:

https://d2mxuefqeaa7sj.cloudfront.net/s_294B82E52F131051DC54044171C3EE7DF0768263E9119A2BDA42103C54C6FFEF_1513954251478_image.png

你會發現 row 每個欄位都相同,為什麼呢? 因為 Id 在這個資料表沒有重複,所以每一筆資料被視為一個分割資料群,而每一群資料都會從 1 開始計算。

我們會一個欄位 Type 進行分割,看看有什麼結果:

https://d2mxuefqeaa7sj.cloudfront.net/s_294B82E52F131051DC54044171C3EE7DF0768263E9119A2BDA42103C54C6FFEF_1513954520996_image.png

你會發現 row 的數字不相同了,開始循序增加,相同 Type 資料會持續遞增,一個完美的資料分群。故我們多一個應用:取出每個分群的第一筆資料,就可以透過這個方法實作。下面範例為取出每種類型相簿中,Id 最大的相簿資料。

SELECT *
  FROM
    (SELECT ROW_NUMBER() OVER(PARTITION BY [TYPE] ORDER BY Id DESC) AS row,
           Id,
           [Type],
           Title,
           [Description]
      FROM [dbo].[Album]) AS RankTable
 WHERE RankTable.[row] = 1

https://d2mxuefqeaa7sj.cloudfront.net/s_294B82E52F131051DC54044171C3EE7DF0768263E9119A2BDA42103C54C6FFEF_1513954941123_image.png


上一篇
03. NEWID () 函數與亂數應用
下一篇
05.DATETIME、DATETIMEOFFSET、SYSDATETIMEOFFSET()、SWITCHOFFSET() 函式與應用
系列文
SQL Server 學習日誌30

尚未有邦友留言

立即登入留言