SQL 查詢結果動態產生流水號：單一陳述式 COUNT(*) 法與 IDENTITY 暫存表法比較

查詢結果常需顯示流水號 (1,2,3...)。雖前端處理最簡單，但有時需在 SQL 層完成。本文比較兩種純 SQL 方案。

方案一：自我 JOIN COUNT(*) 法 (單一陳述式)

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

原理：計算每筆資料「大於等於自身」的筆數即為排名。
缺點：

1. 效能差：O(N²) 自我 JOIN，大量資料極慢。
2. 並列排名：相同值產生相同 Rank (如 1,2,2,4)，缺號。

方案二：IDENTITY 函數 + 暫存表 (多陳述式、高效能)

USE pubs
SELECT IDENTITY(INT, 1, 1) AS Rank, au_lname, au_fname
INTO #tmp
FROM authors
ORDER BY au_lname, au_fname  -- 可指定排序

SELECT * FROM #tmp
DROP TABLE #tmp

優點：

1. 效能佳：單掃描、線性複雜度。
2. 無縫排名：1,2,3,4... 不缺號。
3. 可控排序：ORDER BY 決定流水號順序。

現代版本最佳解法：ROW_NUMBER() 視窗函數 (SQL Server 2005+)

SELECT 
  ROW_NUMBER() OVER (ORDER BY au_lname, au_fname) AS Rank,
  au_lname, au_fname
FROM authors

• 單一陳述式、高效能、支援 PARTITION BY 分組編號、標準 SQL 語法。
• MySQL 8.0+、PostgreSQL、Oracle 皆支援。

選型建議