如何借助ID参考表获取每个id的最新动作记录？

需求说明：从存储所有ID的表一，关联存储动作记录的表二，取出每个ID对应的最新一条动作记录（按date字段排序）。

方法一：子查询+关联查询 #

先通过子查询算出每个ID的最新日期，再关联表二筛选对应记录，同时关联表一确保只包含表一中存在的ID：

SELECT t2.id, t2.date, t2.event
FROM 表一 t1
JOIN 表二 t2 ON t1.id = t2.id
JOIN (
    SELECT id, MAX(date) AS latest_date
    FROM 表二
    GROUP BY id
) t3 ON t2.id = t3.id AND t2.date = t3.latest_date;

方法二：窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数给每个ID的记录按日期倒序编号，取编号为1的最新记录：

SELECT id, date, event
FROM (
    SELECT 
        t2.id, t2.date, t2.event,
        ROW_NUMBER() OVER (PARTITION BY t2.id ORDER BY t2.date DESC) AS rn
    FROM 表一 t1
    JOIN 表二 t2 ON t1.id = t2.id
) temp
WHERE rn = 1;

可选：保留无动作记录的ID #

如果需要显示表一中没有任何动作记录的ID（对应date和event为NULL），把上述SQL中的JOIN替换为LEFT JOIN即可，以方法一为例：

SELECT t1.id, t2.date, t2.event
FROM 表一 t1
LEFT JOIN 表二 t2 ON t1.id = t2.id
LEFT JOIN (
    SELECT id, MAX(date) AS latest_date
    FROM 表二
    GROUP BY id
) t3 ON t2.id = t3.id AND t2.date = t3.latest_date;

内容的提问来源于stack exchange，提问作者Eloi Hizon