MySQL 取每組最新一條記錄：GROUP_CONCAT + SUBSTRING_INDEX 一行搞定

場景

表 t1(uid INT, atime DATETIME, KEY idx_uid(uid))，uid 重複，需取每個 uid 對應 最大 atime 的那一行。

標準思路（連接/子查詢/窗口函數）

-- 1. 衍生表 JOIN
SELECT t1.* FROM t1
JOIN (SELECT uid, MAX(atime) AS max_at FROM t1 GROUP BY uid) t2
  ON t1.uid=t2.uid AND t1.atime=t2.max_at;

-- 2. 相關子查詢
SELECT * FROM t1 t
WHERE atime = (SELECT MAX(atime) FROM t1 WHERE uid=t.uid);

-- 3. MySQL 8.0+ 窗口函數
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY atime DESC) AS rn
  FROM t1
) t WHERE rn=1;

本文黑科技：聚合函數巧取單值

SELECT uid,
       SUBSTRING_INDEX(GROUP_CONCAT(atime ORDER BY atime DESC), ',', 1) AS latest_atime
FROM t1
GROUP BY uid;

原理解析

1. GROUP_CONCAT(atime ORDER BY atime DESC)：組內按時間倒序拼接成 '2013-08-12 11:05:40,2013-08-12 11:05:25'。
2. SUBSTRING_INDEX(..., ',', 1)：取第一個逗號前的子串，即最大時間。

實測輸出

+--------+---------------------+
| uid    | latest_atime        |
+--------+---------------------+
| 1      | 2013-08-12 11:05:40 |
| 2      | 2013-08-12 11:05:43 |
| 3      | 2013-08-12 11:05:48 |
| 5      | 2013-08-12 11:06:03 |
+--------+---------------------+

避坑與限制

適用場景建議

• ✅ 單表、單列取值、組內行數較小（<1000）、MySQL 5.6/5.7 無窗口函數。
• ❌ 大組、需取多列、嚴格生產環境 —— 請用標準 JOIN 或升級 8.0 用窗口函數。