[MSSQL] 將資料以特定條件排序後,加上序號並寫回欄位

Standard

問題:資料原本以 sortid 欄位做排序,後台設計可上下調整排序,但某種情況下 sortid 欄位的值產生重複,造成無法正常變更排序。
因此欲重新以 sortid 排序後,加上 ROW_NUMBER() 索引,並將該索引值寫回 sortid 欄位。

環境:MS SQL SERVER 2005

原本是這樣寫的:

UPDATE [myTable] SET [myTable].sortid = (SELECT ROW_NUMBER() OVER(ORDER BY sortid) AS Serial FROM [myTable] AS B WHERE B.id = [myTable].id)

但這樣只會把 sortid 通通變成 1,怎麼取都只會取到一筆,因此 ROW_NUMBER() 產生出來的序號也只有 1 囉。

所以思路是把排序並加好序號的資料表存放在某個臨時表中,然後再把這個臨時表拿來做子查詢。
由於不是即時去做 ROW_NUMBER(),而是已經「做過」產生好的序號,所以可以確保 sortid 可以被寫入正確的序號值。

  1. 建立一個臨時的表 #tmp,有 idsortidTmp 兩個欄位
    CREATE table #tmp (id bigint, sortidTmp bigint)
    
  2. myTable 資料表的資料以 sortid 排序,並抓出 id (主索引) 與 資料序號 (用 ROW_NUMBER()),做為 #tmp 臨時表的值
    INSERT INTO #tmp SELECT id,ROW_NUMBER() OVER (ORDER BY sortid) FROM [myTable]
    
  3. #tmp 臨時表的值以子查詢擲回 myTable 資料表,將原先的 sortid 欄位的值蓋掉。
    UPDATE [myTable] SET [myTable].sortid = (SELECT sortidTmp FROM #tmp WHERE #tmp.id=[myTable].id)
    
  4. 最後千萬記得把該臨時表卸掉
    DROP TABLE #tmp
    

以上。

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *