如何通过SQL Join获取关联表中的最新记录？

这是个非常常见的业务需求——把主表（Table_One）和子表（Table_two）关联，同时只保留每个主表ID对应的子表最新记录。下面我给你两种实用的解法，都能得到你想要的结果：

先看一下咱们的表结构 #

Table_One（用户表） #

+----+------+
| ID | Name |
+----+------+
| 1  | John |
| 2  | Tom  |
| 3  | Anna |
+----+------+

Table_two（访问记录表） #

+----+----------+-----------+
| ID | Visit ID | Date      |
+----+----------+-----------+
| 1  | 2513     | 5/5/2001  |
| 1  | 84654    | 10/5/2012 |
| 1  | 454      | 4/20/2018 |
| 2  | 754      | 4/5/1999  |
| 2  | 654      | 8/8/2010  |
| 2  | 624      | 4/9/1982  |
| 3  | 7546     | 7/3/1997  |
| 3  | 246574   | 6/4/2015  |
| 3  | 15487    | 3/4/2017  |
+----+----------+-----------+

解法一：使用窗口函数（推荐） #

窗口函数ROW_NUMBER()可以给每个ID的访问记录按日期倒序编号，编号为1的就是最新的那条记录，之后再和主表关联即可。这种方法逻辑清晰，在大多数现代数据库（MySQL 8.0+、PostgreSQL、SQL Server等）都支持：

SELECT t1.ID, t1.Name, t2."Visit ID", t2.Date
FROM Table_One t1
JOIN (
    SELECT 
        ID, 
        "Visit ID", 
        Date,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY STR_TO_DATE(Date, '%m/%d/%Y') DESC) AS rn
    FROM Table_two
) t2 ON t1.ID = t2.ID
WHERE t2.rn = 1;

说明：这里用STR_TO_DATE(Date, '%m/%d/%Y')是因为你的日期格式是月/日/年，需要转换成数据库能识别的日期类型来正确排序，如果你的Date字段已经是日期类型，直接用Date DESC就行。

解法二：使用子查询找最新日期 #

如果你的数据库不支持窗口函数（比如MySQL 5.x版本），可以先通过子查询找到每个ID的最新访问日期，再关联子表和主表：

SELECT t1.ID, t1.Name, t2."Visit ID", t2.Date
FROM Table_One t1
JOIN Table_two t2 ON t1.ID = t2.ID
JOIN (
    SELECT ID, MAX(STR_TO_DATE(Date, '%m/%d/%Y')) AS latest_date
    FROM Table_two
    GROUP BY ID
) t3 ON t2.ID = t3.ID AND STR_TO_DATE(t2.Date, '%m/%d/%Y') = t3.latest_date;

说明：这个方法先分组找到每个用户的最新日期，再匹配对应的访问记录，最后和主表关联。同样，如果Date字段是日期类型，就不用STR_TO_DATE转换了。

最终期望结果 #

两种方法都会得到你想要的输出：

+----+------+----------+-----------+
| ID | Name | Visit ID | Date      |
+----+------+----------+-----------+
| 1  | John | 454      | 4/20/2018 |
| 2  | Tom  | 654      | 8/8/2010  |
| 3  | Anna | 246574   | 6/4/2015  |
+----+------+----------+-----------+

内容的提问来源于stack exchange，提问作者Bhargav Andipara