从关联表选取单条行：查询Person表每人对应Address表最新地址

错误原因 #

你原SQL的核心问题出在子查询的关联条件上：子查询的WHERE条件里引用的a是外层LEFT JOIN的Address表别名，没有和子查询自身的Address表做人员ID关联，导致取到的最大address_id不符合预期，最终匹配不到地址数据。

修正方案1：调整子查询关联逻辑 #

给子查询内的Address表单独设置别名，明确关联规则即可正常运行：

SELECT
  p.person_id AS id,
  p.first_name AS first_name,
  p.last_name AS last_name,
  a.address AS address
FROM Person p
LEFT JOIN Address a ON
  a.address_id = (
    SELECT MAX(a_sub.address_id)
    FROM Address a_sub
    WHERE a_sub.person_id = p.person_id
  )

修正方案2：窗口函数写法（扩展性更强） #

如果后续需要同时取最新地址的其他字段（比如城市、邮编），用窗口函数的写法更易维护：

SELECT
  p.person_id AS id,
  p.first_name,
  p.last_name,
  addr.address
FROM Person p
LEFT JOIN (
  SELECT 
    person_id,
    address,
    ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY address_id DESC) AS rn
  FROM Address
) addr ON addr.person_id = p.person_id AND addr.rn = 1

性能优化建议 #

如果Address表数据量较大，建议给person_id、address_id字段加联合索引，两种写法的执行效率都会有明显提升。

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