MySQL 自訂變數實現查詢結果動態排名 (ROW_NUMBER 替代方案)

MySQL 自訂變數實現查詢結果動態排名 (ROW_NUMBER 替代方案)

MySQL 8.0 前無視窗函數，常用 @變數 模擬 ROW_NUMBER()、RANK()、DENSE_RANK()。即使 8.0+ 已支援標準語法，變數寫法在舊版遷移、複雜排序邏輯中仍有參考價值。本文示範基於 Roll_No 排序並產生連續排名的完整範例。

範例資料

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  last_name VARCHAR(50),
  roll_no INT UNIQUE
);
INSERT INTO students (last_name, roll_no) VALUES
('Chen', 101), ('Lin', 103), ('Wang', 102), ('Huang', 105), ('Wu', 104);

基礎排名 (ROW_NUMBER 效果)

SET @rank = 0;
SELECT
  @rank := @rank + 1 AS rank,
  last_name,
  roll_no
FROM students
ORDER BY roll_no;

結果：

進階：分組排名 (PARTITION BY 效果)

按 class_id 分組，組內按 score 降序排名：

SET @rank = 0, @prev_class = NULL;
SELECT
  class_id,
  student_name,
  score,
  @rank := IF(@prev_class = class_id, @rank + 1, 1) AS rank,
  @prev_class := class_id
FROM exam_results
ORDER BY class_id, score DESC;

並列排名 (RANK / DENSE_RANK 效果)

-- RANK: 並列後跳號 (1,2,2,4)
SET @rank = 0, @prev_score = NULL, @row = 0;
SELECT
  student_name, score,
  @row := @row + 1 AS row_num,
  @rank := IF(@prev_score = score, @rank, @row) AS rank,
  @prev_score := score
FROM exam_results
ORDER BY score DESC;

-- DENSE_RANK: 並列不跳號 (1,2,2,3)
SET @rank = 0, @prev_score = NULL;
SELECT
  student_name, score,
  @rank := IF(@prev_score = score, @rank, @rank + 1) AS dense_rank,
  @prev_score := score
FROM exam_results
ORDER BY score DESC;

MySQL 8.0+ 標準寫法 (推薦)

SELECT
  last_name,
  roll_no,
  ROW_NUMBER() OVER (ORDER BY roll_no) AS rn,
  RANK() OVER (ORDER BY roll_no) AS rk,
  DENSE_RANK() OVER (ORDER BY roll_no) AS drk
FROM students;

優點：語意清晰、優化器可下推、支援 PARTITION BY、FRAME 子句，效能優於變數法。

避坑指南

• 變數初始化：必須在同一 Session 先 SET @var = 0，否則為 NULL 導致運算失敗。
• ORDER BY 位置：排名邏輯強依賴輸入順序，ORDER BY 必須在外層或子查詢中保證。
• 複用風險：同一 Session 多個查詢共用變數會互相干擾，建議用 SELECT ... FROM (SELECT @rank:=0) r JOIN ... 子查詢初始化，隔離作用域。
• Replication 不安全：變數賦值語句在 Statement-based 複寫下不可預測，生產環境請用 Row-based 或改用 8.0 視窗函數。

結語：用戶變數排名是 MySQL 5.x 時代的智慧結晶，理解其原理有助於維護舊系統；新專案請直接擁抱 WINDOW FUNCTIONS，享受標準 SQL 的表達力與優化器紅利。