MySQL 變數實現查詢結果排名：ROW_NUMBER() 替代方案

場景

MySQL 8.0 前無 ROW_NUMBER() 窗口函數，需用用戶變數模擬排名功能。

基本語法

-- 1. 初始化變數
SET @rank = 0;

-- 2. 查詢中遞增
SELECT @rank := @rank + 1 AS rank, column1, column2
FROM table_name
ORDER BY sort_column;

完整範例：學生成績排名

-- 建表測試
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    score INT
);

INSERT INTO students (name, score) VALUES 
('Alice', 95), ('Bob', 87), ('Charlie', 92), ('David', 87), ('Eve', 98);

-- 排名查詢（分數降序，同分同排名？需進階邏輯）
SET @rank = 0;
SET @prev_score = NULL;
SET @real_rank = 0;

SELECT 
    @real_rank := IF(@prev_score = score, @real_rank, @rank) AS rank,
    @rank := @rank + 1 AS row_num,
    @prev_score := score AS score,
    name
FROM students
ORDER BY score DESC;

輸出結果

+------+---------+-------+--------+
| rank | row_num | score | name   |
+------+---------+-------+--------+
|    1 |       1 |    98 | Eve    |
|    2 |       2 |    95 | Alice  |
|    3 |       3 |    92 | Charlie|
|    4 |       4 |    87 | Bob    |
|    4 |       5 |    87 | David  |
+------+---------+-------+--------+

進階排名類型實現

1. 密集排名 - 同分同名，下一名遞增 1

SET @rank = 0; SET @prev = NULL;
SELECT 
    @rank := IF(@prev = score, @rank, @rank + 1) AS dense_rank,
    @prev := score AS score,
    name
FROM students ORDER BY score DESC;

2. 競爭排名 - 同分同名，下一名跳過（標準排名）

SET @rank = 0; SET @row = 0; SET @prev = NULL;
SELECT 
    @rank := IF(@prev = score, @rank, @row + 1) AS competition_rank,
    @row := @row + 1 AS row_num,
    @prev := score AS score,
    name
FROM students ORDER BY score DESC;

3. 分組內排名（如：各班級內排名）

SET @rank = 0; SET @prev_class = NULL; SET @prev_score = NULL;
SELECT 
    @rank := IF(@prev_class = class AND @prev_score = score, @rank,
                IF(@prev_class = class, @rank + 1, 1)) AS class_rank,
    @prev_class := class AS class,
    @prev_score := score AS score,
    name
FROM students
ORDER BY class, score DESC;

MySQL 8.0+ 窗口函數：原生支援

-- 標準語法，效能更好、語意清晰
SELECT 
    name, score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK() OVER (ORDER BY score DESC) AS competition_rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank,
    PERCENT_RANK() OVER (ORDER BY score DESC) AS percent_rank
FROM students;

變數法注意事項與坑點

1. 順序敏感：ORDER BY 必須在變數計算之前，子查詢包裹確保順序
2. 初始化必要：每次查詢前必須 SET @var = 0，否則殘留值導致錯誤
3. 不可用於 WHERE/GROUP BY/HAVING：變數賦值在 SELECT 列表執行，無法反向過濾
4. 複製表/視圖失效：CREATE TABLE t2 SELECT @rank:=@rank+1 ... 結果不可預測
5. 並行安全：用戶變數為會話級，連線池複用時需確保初始化

最佳實踐建議

• MySQL 8.0+：**直接使用窗口函數**，標準、高效、可維護
• MySQL 5.7/5.6：用戶變數法僅用於簡單報表，核心業務建議應用層處理
• 複雜排名邏輯（分組、分頁、同分處理）建議封裝為儲存程序或視圖