您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

SQL Server 2005 依指定欄位排序產生流水號：CTE + ROW_NUMBER() 更新寫法

編輯：SQL Server 2005

需求：依特定欄位排序 (如人氣值降冪)，將排序後的順序號寫入資料表的編號欄位。SQL Server 2005 可用 CTE 搭配 ROW_NUMBER() 完成單一陳述式更新，無需游標/暫存表。

通用語法範本

WITH cte_name AS (
    SELECT 編號欄位, 
           ROW_NUMBER() OVER (ORDER BY 排序欄位 DESC) AS RowNum
    FROM 資料表名
)
UPDATE cte_name
SET 編號欄位 = RowNum;

實戰範例：社團依人氣值排名

-- 表結構
CREATE TABLE club_Groups (
    GroupID INT PRIMARY KEY,
    GroupName NVARCHAR(100),
    Popularity INT,      -- 人氣值
    RankNum INT          -- 排名欄位 (待更新)
);

-- 依人氣值降冪產生排名
WITH grp AS (
    SELECT RankNum, 
           ROW_NUMBER() OVER (ORDER BY Popularity DESC) AS RowNum
    FROM club_Groups
)
UPDATE grp
SET RankNum = RowNum;

進階變化

分組排名 (每個分類別獨立排名)

WITH grp AS (
    SELECT RankNum,
           ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY Popularity DESC) AS RowNum
    FROM club_Groups
)
UPDATE grp SET RankNum = RowNum;

並列同名次 (RANK / DENSE_RANK)

-- RANK: 1,2,2,4 (跳過)
WITH grp AS (
    SELECT RankNum, RANK() OVER (ORDER BY Popularity DESC) AS RowNum FROM club_Groups
)
UPDATE grp SET RankNum = RowNum;

-- DENSE_RANK: 1,2,2,3 (不跳過)
WITH grp AS (
    SELECT RankNum, DENSE_RANK() OVER (ORDER BY Popularity DESC) AS RowNum FROM club_Groups
)
UPDATE grp SET RankNum = RowNum;

多欄位排序 (人氣值相同時依建立時間)

WITH grp AS (
    SELECT RankNum,
           ROW_NUMBER() OVER (ORDER BY Popularity DESC, CreateDate ASC) AS RowNum
    FROM club_Groups
)
UPDATE grp SET RankNum = RowNum;

效能考量

ROW_NUMBER() 需排序，確保 ORDER BY 欄位有索引 (Popularity DESC 或 Popularity DESC, CreateDate ASC)
大量資料更新會產生大量記錄檔，考慮分批更新 (每批 5000-10000 列)
更新前備份資料或在交易中測試：BEGIN TRAN ... UPDATE ... ROLLBACK