使用GROUP BY子查询关联表后出现重复行的原因排查

问题背景 #

需要获取各产品ID的最新更新状态，现有Products和Transactions两张表。单独执行select ID, max(Last_Updated) from Transactions group by ID能得到正确的各产品最新时间行数，但关联Products表补充信息时，部分ID返回两行（通常是最近两次状态），而非预期的仅最新日期行。

执行的关联查询 #

select p.ID, p.Product, T.Last_Updated from Products P
inner join Transactions T on p.ID = T.ID
where p.ID in (1,2,3)
and T.Last_Updated in (select ID, max(Last_Updated) from Transactions group by ID)
order by p.ID desc

表结构 #

Products表 #

Transactions表 #

实际结果 #

预期结果 #

问题原因 #

原查询的核心错误在于T.Last_Updated in (select ID, max(Last_Updated) from Transactions group by ID)这一条件：

• 子查询返回了ID和max(Last_Updated)两列，但IN操作符仅能匹配单列值，数据库会忽略子查询的第一列（ID），仅用max(Last_Updated)的集合进行匹配。
• 这导致只要某条交易的Last_Updated等于任意产品的最新更新时间，就会被筛选出来，而非仅匹配当前产品自身的最新时间。

解决方案 #

方案1：使用多列IN匹配（支持MySQL 8.0+、PostgreSQL等数据库） #

直接让(T.ID, T.Last_Updated)匹配子查询返回的ID和对应最新时间的组合：

select p.ID, p.Product, T.Status, T.Last_Updated 
from Products P
inner join Transactions T on p.ID = T.ID
where p.ID in (1,2,3)
and (T.ID, T.Last_Updated) in (select ID, max(Last_Updated) from Transactions group by ID)
order by p.ID desc

方案2：先预查询最新时间再关联 #

先通过子查询获取每个ID的最新时间，再依次关联Products和Transactions表：

select p.ID, p.Product, T.Status, T.Last_Updated
from Products p
inner join (
    select ID, max(Last_Updated) as Last_Updated
    from Transactions
    group by ID
) latest on p.ID = latest.ID
inner join Transactions T on latest.ID = T.ID and latest.Last_Updated = T.Last_Updated
where p.ID in (1,2,3)
order by p.ID desc

方案3：使用窗口函数（推荐，支持绝大多数现代数据库） #

通过ROW_NUMBER()窗口函数给每个产品的交易按时间倒序编号，取编号为1的最新行：

select ID, Product, Status, Last_Updated
from (
    select p.ID, p.Product, T.Status, T.Last_Updated,
           ROW_NUMBER() over (partition by p.ID order by T.Last_Updated desc) as rn
    from Products p
    inner join Transactions T on p.ID = T.ID
    where p.ID in (1,2,3)
) t
where rn = 1
order by ID desc

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