您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 生成行號/排名序列的四種實現方案

編輯：MySQL 資料庫相關知識

場景與測試數據

CREATE TABLE tbl (id INT PRIMARY KEY, col INT);
INSERT INTO tbl VALUES (1,26),(2,46),(3,35),(4,68),(5,93),(6,92);
-- 排序後：id=1(26), 3(35), 2(46), 4(68), 6(92), 5(93)

方案對比

1. 應用層計數（效能最優、最簡單）

# PHP 示例
$rowNum = 0;
while ($row = mysqli_fetch_assoc($result)) {
    $rowNum++;
    echo $rowNum, $row['id'], $row['col'];
}

✅ 零資料庫負擔、網絡傳輸無額外列
✅ 適用於所有語言框架
❌ 無法在純 SQL 中複用（如視圖、存儲過程）

2. 用戶變量 @rowNum（MySQL 8.0 前標準寫法）

SET @rowNum = 0;
SELECT @rowNum := @rowNum + 1 AS rownum, id, col
FROM tbl ORDER BY col;

⚠️ 連接級變量：同連接複用需重置，連接池場景極易出 Bug
⚠️ MySQL 8.0.2+ 棄用，建議遷移至窗口函數

3. 自聯接/笛卡爾積（教科書式但低效）

SELECT a.id, a.col, COUNT(*) AS rownum
FROM tbl a JOIN tbl b ON a.col >= b.col
GROUP BY a.id, a.col ORDER BY a.col;

❌ O(N²) 複雜度，萬行以上不可用
❌ 相同 col 值會產生重複排名

4. 相關子查詢（可讀性尚可，效能同自聯接）

SELECT a.*, 
  (SELECT COUNT(*) FROM tbl WHERE col <= a.col) AS rownum
FROM tbl a ORDER BY col;

現代最佳方案：窗口函數（MySQL 8.0+）

SELECT 
  id, col,
  ROW_NUMBER() OVER (ORDER BY col) AS rownum,
  RANK() OVER (ORDER BY col) AS rnk,
  DENSE_RANK() OVER (ORDER BY col) AS drnk
FROM tbl;

函數	行為	適用場景
ROW_NUMBER()	連續唯一序號 1,2,3...	分頁、導出編號
RANK()	並列同名次，跳過下一名 1,2,2,4	競賽排名
DENSE_RANK()	並列同名次，不跳過 1,2,2,3	成績等級

分組內排名（TOP N 問題）

SELECT * FROM (
  SELECT 
    id, category, score,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY score DESC) AS rn
  FROM scores
) t WHERE rn <= 3;  -- 每類前 3 名