SQL ROW_NUMBER() 與資料排序設定

緣起:

    前陣子有站台的相關檔案排序出現了問題，我原本想說，這東西會不好解決，但後來同事有教我，可以用 ROW_NUMBER() 來重新設定排序。

    自己回去弄懂了後，覺得這東西真好用，所以想寫一篇文章來記錄。

情境:

    我在自己筆電上弄了一張簡化的表格來模擬情境，這張表叫 MyFile

    SN  -> 流水號主 Key，FileName -> 檔案名稱，Sort -> 此筆資料在群組裡的排序。ParentSN 則是記錄此筆資料是依附於哪筆訊息。

一筆訊息可以有 0~N 筆相關檔案

    像這樣，圖中的每個相關檔案都是一筆 MyFile，藉由 ParentSN 來得知它屬於哪一筆訊息。不過，訊息的表格不是重點，所以我也就沒創表，重點都是在處理 MyFile。

    我有手動 Key 一些資料進去，將它們按照 ParentSN 與 Sort 排序後，呈現出來的資料是這樣

    從 ParentSN 可以看出，有五筆訊息資料，MyFile 被分為五個群，各群的排序也都正常，沒有重複值。

問題:

    我們碰到的問題就是，Sort 被擾亂，每群 (依 ParentSN 來分群) 裡面有重複的 Sort 值，導致後續在網頁上操作時，更新 MyFile 排序的功能異常。

    我下 SQL 來擾亂排序，最後的表格會長這樣

    可以看到，某些群的 Sort 出現了重複的狀況。

ROW_NUMBER:

    參考資料。ROW_NUMBER() 會搭配 Over 來使用，指令的架構會是這樣

select *, ROW_NUMBER() OVER (PARTITION BY [分群用欄位] ORDER BY [排序用欄位]) as [ROW_NUMBER()的欄位名稱] from [目標表格]

    我試著下指令

SELECT *, ROW_NUMBER() OVER (PARTITION BY ParentSN ORDER By Sort) AS New_Sort from dbo.MyFile

    可以看到，用 ROW_NUMBER() 產生的 New_Sort 會依群組來給每筆資料編號，編號由 1 開始，每次遞增 1。

    除了原本的排序紀錄，如果想再依據資料加入的順序來調整新排序的話，還能在 Order By 那邊再多給一個 SN 的參考欄位。

SELECT *, ROW_NUMBER() OVER (PARTITION BY ParentSN ORDER BY Sort, SN) AS New_Sort from dbo.MyFile

用 New_Sort 更新 Sort:

    這邊會用到 SQL 的 WITH 語法，先把 SN 與 New_Sort 選出來，這邊先看一下那張 tmpFile 的樣子。

WITH tmpFile AS (
	SELECT SN,
	ROW_NUMBER() OVER (PARTITION BY ParentSN ORDER By Sort, SN) New_Sort
	FROM dbo.MyFile
) SELECT * FROM tmpFile

    再來把下方的 SELECT 語法改成 UPDATE，透過 JOIN SN 來把 New_Sort 更新到 Sort。

WITH tmpFile AS (
	SELECT SN,
	ROW_NUMBER() OVER (PARTITION BY ParentSN ORDER By Sort, SN) New_Sort
	FROM dbo.MyFile
)

UPDATE dbo.MyFile SET Sort = tmpFile.New_Sort
FROM dbo.MyFile AS myFile
join tmpFile ON myFile.SN = tmpFile.SN

    指令執行完之後，就可以看到排序變正常了