mysql - 子查询左连接引用父ID

Question

我正在尝试查询以获取每个用户的最新汽车：

select * from users 
    left join 
       (select cars.* from cars 
        where cars.userid=users.userid 
        order by cars.year desc limit 1) as cars
    on cars.userid=users.userid

看起来它在 where 子句中显示未知列“users.userid”，我试图删除 cars.userid=users.userid 部分，但随后它只获取 1 辆最新汽车，并将其粘贴到每个用户身上。

有什么办法可以完成我所追求的吗？谢谢！！

标签： mysqlsqljoinselectgreatest-n-per-group

Answer 1

为此，我通常使用row_number()：

select *
from users u left join
     (select c.* , row_number() over (partition by c.userid order by c.year desc) as seqnum
      from cars c
     ) c 
     on c.userid = u.userid and c.seqnum = 1;