如何LEFT JOIN两表并仅关联第二表最新行及条件筛选？

需求一：关联人员与最新发色记录 #

利用窗口函数ROW_NUMBER()可以高效定位每个人员的最新发色记录，替代循环判断的低效方式，SQL语句如下：

SELECT p.People_index, p.Name, hc.Color, hc.`Color-change time`
FROM People p
LEFT JOIN (
    SELECT 
        People_index, 
        Color, 
        `Color-change time`,
        ROW_NUMBER() OVER (PARTITION BY People_index ORDER BY `Color-change time` DESC) AS rn
    FROM `Hair colors`
) hc ON p.People_index = hc.People_index AND hc.rn = 1;

关键逻辑说明

• PARTITION BY People_index：按人员ID分组，确保每个人员的发色变更记录单独排序
• ORDER BY \Color-change time` DESC：*按变更时间倒序排列*，最新的记录会被标记为rn=1`
• hc.rn = 1：仅保留每个人员的最新发色记录
• LEFT JOIN保证没有任何发色变更记录的人员也会出现在结果中

需求二：筛选最新发色为指定值的人员 #

在需求一的查询基础上，直接添加WHERE条件筛选目标发色即可：

SELECT p.People_index, p.Name, hc.Color, hc.`Color-change time`
FROM People p
LEFT JOIN (
    SELECT 
        People_index, 
        Color, 
        `Color-change time`,
        ROW_NUMBER() OVER (PARTITION BY People_index ORDER BY `Color-change time` DESC) AS rn
    FROM `Hair colors`
) hc ON p.People_index = hc.People_index AND hc.rn = 1
WHERE hc.Color = '绿色';

补充场景

如果需要包含无发色变更记录的人员（比如允许筛选结果中包含Color为NULL的条目），可以修改WHERE子句：

WHERE hc.Color = '绿色' OR hc.Color IS NULL;

兼容低版本数据库方案 #

如果你的数据库版本较低不支持窗口函数，也可以用MAX()子查询实现：

SELECT p.People_index, p.Name, hc.Color, hc.`Color-change time`
FROM People p
LEFT JOIN `Hair colors` hc 
    ON p.People_index = hc.People_index
    AND hc.`Color-change time` = (
        SELECT MAX(`Color-change time`) 
        FROM `Hair colors` 
        WHERE People_index = p.People_index
    );

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