LEFT JOIN关联person与activities表查询每人最新活动（含无活动人员）

你第二次写的SQL出现Axel记录丢失的核心原因是：LEFT JOIN生成的中间结果里，无活动记录的人员对应的a表所有字段都是NULL，WHERE子句中a.id = (子查询结果)的判断对NULL值会返回false，直接把这部分行过滤掉了。同时注意你写SQL时存在两个笔误：活动表实际表名为activities、活动表主键字段是activity_id不是id，后续方案已经修正了这两个问题。

下面给出三种常用的实现方式，你可以根据自己使用的数据库版本选择：

方案1：把过滤条件移到LEFT JOIN的ON子句（写法最简单） #

直接把最大activity_id的过滤逻辑放到关联条件里，就不会过滤掉左表的行：

SELECT p.id, p.name, a.activity_id, a.activity_type
FROM person p
LEFT JOIN activities a 
ON p.id = a.person_id 
AND a.activity_id = (SELECT MAX(activity_id) FROM activities a2 WHERE a2.person_id = p.id)
ORDER BY p.id;

方案2：用ROW_NUMBER窗口函数（逻辑清晰、扩展性强） #

适合MySQL8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库，后续如果需要调整为取每个用户最新N条活动也非常方便：

WITH ranked_activities AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY activity_id DESC) AS rn
    FROM activities
)
SELECT p.id, p.name, a.activity_id, a.activity_type
FROM person p
LEFT JOIN ranked_activities a ON p.id = a.person_id AND a.rn = 1
ORDER BY p.id;

方案3：先聚合取最大activity_id再关联（全版本兼容） #

适合所有数据库版本，兼容性最高：

SELECT p.id, p.name, a.activity_id, a.activity_type
FROM person p
LEFT JOIN (
    SELECT person_id, MAX(activity_id) AS max_activity_id
    FROM activities
    GROUP BY person_id
) max_a ON p.id = max_a.person_id
LEFT JOIN activities a ON max_a.max_activity_id = a.activity_id
ORDER BY p.id;

以上三种写法返回的结果都和你期望的完全一致：

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