MySQL 分組查詢取各用戶最高等級記錄（排名榜實作）

需求場景

資料表記錄多位用戶的遊戲等級變更歷程（同一 uid 有多筆 level 記錄），需求是取出每位用戶的最高等級那一筆資料；若最高等級相同，則以時間較早者優先。這是典型的「分組取最大值並保留完整欄位」問題，常用於排行榜、最新狀態查詢等場景。

範例資料

+------+-------+--------------+---------------------+
| uid  | level | role         | time                |
+------+-------+--------------+---------------------+
| 7    | 1     | 搖滾聖魔     | 2014-06-12 15:01:05 |
| 1134 | 4     | 唯我獨尊     | 2014-06-12 15:02:38 |
| 1134 | 4     | 唯我獨尊     | 2014-06-12 15:02:39 |
| 7    | 3     | 搖滾聖魔     | 2014-06-12 15:02:59 |
| 5    | 3     | 韻兒鈴♦      | 2014-06-12 15:04:09 |
| 7363 | 6     | 詩荷冰月     | 2014-06-12 15:04:23 |
| 7    | 16    | 搖滾聖魔     | 2014-06-12 15:05:46 |
| 7    | 26    | 搖滾聖魔     | 2014-06-12 15:08:36 |
| 6684 | 8     | つ道遠虛空つ | 2014-06-12 15:08:45 |
| 5    | 12    | 韻兒鈴♦      | 2014-06-12 15:09:47 |
| 7    | 26    | 搖滾聖魔     | 2014-06-12 15:13:38 |
| 5    | 25    | 韻兒鈴♦      | 2014-06-12 15:14:48 |
| 5    | 28    | 韻兒鈴♦      | 2014-06-12 15:18:08 |
| 5    | 37    | 韻兒鈴♦      | 2014-06-12 15:27:37 |
| 5    | 41    | 韻兒鈴♦      | 2014-06-12 15:41:56 |
+------+-------+--------------+---------------------+

解法：子查詢 + 排序 + 分組

核心邏輯分兩步：

1. 先依 uid 升序、level 降序、time 升序排序，確保每組第一筆為最高等級且最早時間。
2. 外層以 uid 分組，直接取第一筆（MySQL 允許非 ONLY_FULL_GROUP_BY 模式下這樣寫）。

SQL 範例

SELECT * FROM (
    SELECT uid, level, role, time
    FROM `game_level_log`
    ORDER BY uid ASC, level DESC, time ASC
) AS t
GROUP BY uid
ORDER BY level DESC, time ASC
LIMIT 30;

注意： MySQL 5.7+ 預設開啟 ONLY_FULL_GROUP_BY，上述寫法可能報錯。嚴謹寫法請改用 ROW_NUMBER() 窗口函數（MySQL 8.0+）或關聯子查詢。

進階：窗口函數寫法

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY uid ORDER BY level DESC, time ASC) AS rn
    FROM `game_level_log`
)
SELECT uid, level, role, time
FROM ranked
WHERE rn = 1
ORDER BY level DESC, time ASC
LIMIT 30;

相關技巧補充

取得某欄位最大值對應的完整行

若欄位為 VARCHAR 需轉數值比較，可用 CAST(id AS UNSIGNED) 或 id+0：

SELECT * FROM music WHERE id+0 = (SELECT MAX(id+0) FROM music);

分組後取各組最大值的所有欄位

SELECT a.* FROM #a a
JOIN (
    SELECT address, MAX(fenshu) AS max_fenshu
    FROM #a
    GROUP BY address
) b ON a.address = b.address AND a.fenshu = b.max_fenshu;