MySQL 實現排名查詢：模擬 Oracle ROW_NUMBER / RANK / DENSE_RANK

場景

MySQL 5.7/8.0 前無窗口函數，需用相關子查詢或變數模擬排名。MySQL 8.0+ 已原生支援 ROW_NUMBER()、RANK()、DENSE_RANK()。

測試資料

CREATE TABLE animals_inno (
  grp VARCHAR(20), id INT, name VARCHAR(20),
  created DATETIME, modified DATETIME
);
INSERT INTO animals_inno VALUES
('mammal',1,'dog',NULL,NULL), ('mammal',2,'cat',NULL,NULL),
('bird',3,'penguin',NULL,NULL), ('fish',4,'lax',NULL,NULL),
('mammal',5,'whale',NULL,NULL), ('bird',6,'ostrich',NULL,NULL),
('fish',7,NULL,NULL,NULL), ('fish',8,'',NULL,NULL);

1. 全表依 grp 排序：占位排名 (對應 RANK)

SELECT grp, name, id,
       (SELECT COUNT(*) FROM animals_inno WHERE grp < a.grp) + 1 AS place
FROM animals_inno a
ORDER BY place;
-- bird=1, fish=4, mammal=7 (同組佔位)

2. 全表依 grp 排序：不佔位排名 (對應 DENSE_RANK)

SELECT grp, name, id,
       (SELECT COUNT(DISTINCT grp) FROM animals_inno WHERE grp < a.grp) + 1 AS place
FROM animals_inno a
ORDER BY place;
-- bird=1, fish=2, mammal=3

3. 組內依 id 排序：占位排名 (組內 RANK)

SELECT grp, name, id,
       (SELECT COUNT(*) FROM animals_inno WHERE grp = a.grp AND id < a.id) + 1 AS place
FROM animals_inno a
ORDER BY grp, place;
-- fish: 1,2,3 | mammal: 1,2,3 | bird: 1,2

4. 組內依 id 排序：不佔位排名 (組內 DENSE_RANK)

SELECT grp, name, id,
       (SELECT COUNT(DISTINCT id) FROM animals_inno WHERE grp = a.grp AND id < a.id) + 1 AS place
FROM animals_inno a
ORDER BY grp, place;

MySQL 8.0+ 原生寫法 (推薦)

SELECT grp, name, id,
       ROW_NUMBER() OVER (PARTITION BY grp ORDER BY id) AS rn,
       RANK()       OVER (PARTITION BY grp ORDER BY id) AS rk,
       DENSE_RANK() OVER (PARTITION BY grp ORDER BY id) AS drk
FROM animals_inno;

效能對比

遷移建議

• MySQL 5.7 專案規劃升級 8.0 以獲得原生窗口函數、CTE、JSON 功能。
• 暫時無法升級時，小結果集 用相關子查詢可讀性佳；大結果集 改用變數法或應用層分頁處理。
• Oracle 遷移 MySQL 時，ROW_NUMBER → ROW_NUMBER()，RANK → RANK()，DENSE_RANK → DENSE_RANK() 直接對應。