SQL 查詢結果添加自動編號的兩種實用技巧

前言

在開發過程中，經常需要為查詢結果集添加一列類似 Identity 的自動遞增編號。雖然在客戶端代碼中實現並不困難，但有時為了復用現有類庫、避免額外編碼，必須在 SQL 層面解決。本文介紹兩種在 SQL Server 中實現自動編號的方法，並分析其優缺點。

方法一：自我連接計數法（單條 SQL 完成）

核心原理：統計結果集中「大於等於當前記錄」的記錄條數，即可得到該記錄的排名。

USE pubs
SELECT COUNT(*) AS Rank, a1.au_lname, a1.au_fname
FROM authors a1, authors a2
WHERE a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
GROUP BY a1.au_lname, a1.au_fname
ORDER BY Rank

缺點：

• 性能較差，屬於笛卡爾積查詢，數據量大時極慢。
• 遇到重複值會產生並列排名（如出現兩個 2，卻沒有 3），無法保證連續唯一編號。

方法二：IDENTITY 函數配合臨時表（性能更優）

SQL Server 提供 IDENTITY 函數可生成標識列，但限制為只能用於 SELECT INTO 語句，因此需引入臨時表。

USE pubs
SELECT IDENTITY(INT, 1, 1) AS Rank, au_lname, au_fname INTO #tmp FROM authors
SELECT * FROM #tmp
DROP TABLE #tmp

優點： 性能與適用性均優於方法一，能生成連續唯一編號。
缺點： 需多條 SQL 語句完成，無法在單一查詢中直接使用。

最佳實踐建議

若業務允許，仍建議在客戶端應用層添加行號，避免數據庫層額外開銷；必須在 SQL 端解決時，優先選擇方法二。