MySQL 查詢每個用戶最新一條登錄記錄：子查詢倒序 + GROUP BY 高效寫法

需求分析

表 loginlog 記錄用戶操作日誌，id 自增主鍵，username 重復。需求：一條 SQL 獲取每個用戶最新（id 最大）的一條記錄，包含 username、operate，結果不重復用戶。

推薦寫法：派生表 + GROUP BY

SELECT a.*
FROM (
    SELECT * FROM loginlog ORDER BY id DESC
) a
GROUP BY a.username;

原理解析

1. 子查詢 ORDER BY id DESC 將全表按時間倒序（最新在前）。
2. 外層 GROUP BY username 分組時，MySQL（開啟 ONLY_FULL_GROUP_BY 前）會**取每組第一行**（即最新記錄）的所有字段值。
3. 若嚴格模式 (ONLY_FULL_GROUP_BY) 報錯，改用聚合函數或窗口函數（見下）。

標準 SQL 寫法（兼容嚴格模式、所有主流數據庫）

方案一：ROW_NUMBER() 窗口函數

SELECT id, username, operate
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY username ORDER BY id DESC) AS rn
    FROM loginlog
) t
WHERE rn = 1;

優點：標準、可移植、可擴展取「前 N 條」。MySQL 8.0+、PostgreSQL、SQL Server、Oracle 均支持。

方案二：關聯子查詢（MySQL 5.7 兼容）

SELECT l1.*
FROM loginlog l1
JOIN (
    SELECT username, MAX(id) AS max_id
    FROM loginlog
    GROUP BY username
) l2 ON l1.username = l2.username AND l1.id = l2.max_id;

優點：利用索引 (username, id) 極快，無臨時表/文件排序。

方案三：LEFT JOIN 自連接排除法

SELECT l1.*
FROM loginlog l1
LEFT JOIN loginlog l2
  ON l1.username = l2.username AND l1.id < l2.id
WHERE l2.id IS NULL;

缺點：大表性能差（笛卡爾積），不推薦生產環境。

索引建議

CREATE INDEX idx_username_id ON loginlog(username, id DESC);

覆蓋 GROUP BY username、MAX(id)、ORDER BY id DESC 所有場景。

總結選型表

掌握「分組取最新」三大範式，應對 99% 同類需求。