如何在MySQL中根据其最大日期只选择左表的一条记录？

2024-05-21 • 问答

下面是我的数据库结构

User
id       name
1        John
2        Doe
3        Smitt


Post
id       user_id      text
1        1            Hello
2        1            World
3        2            How are you?
4        3            Whatsup!
5        3            High five!


Comment
id       post_id      text                        created_at
1        1            Comment on Hello            2019-12-01
2        1            Another comment on Hello    2019-12-02
3        2            Comment on World            2019-12-03
4        1            Latest comment on Hello     2019-12-04
5        1            Some comment                2019-12-05
6        5            Five highs!                 2019-12-06
7        4            Same old,same old!         2019-12-07

如何基于MySQL中的Comment获取仅包含一个MAX(created_at)记录的用户列表，如下所示？

Result
id        name       comment_text          created_at
1         John       Some comment          2019-12-05
2         Doe        NULL                  NULL
3         Smitt      Same old,same old!   2019-12-07

条件：：由于另一个用例，流程必须从User表转到Comment表，而不是相反！

您可以left join，使用相关子查询来检索当前用户最新帖子的ID作为加入条件：

select
    u.id,u.name,c.text comment_text,c.created_at
from user u
left join comment c 
    on c.id = (
        select c1.id
        from post p1
        inner join comment c1 on c1.post_id = p1.id
        where p1.user_id = u.id
        order by c1.created_at desc
        limit 1
    )

Demo on DB Fiddle ：

  id | name  | comment_text        | created_at
---: | :---- | :------------------ | :---------
   1 | John  | Some comment        | 2019-12-05
   2 | Doe   | null                | null      
   3 | Smitt | Same old,same old! | 2019-12-07

或者，如果您正在运行MySQL 8.0，则可以使用row_number()：

select id,name,comment_text,created_at
from (
    select
        u.id,c.created_at,row_number() over(partition by u.id order by c.created_at desc) rn
    from user u
    left join post p on p.user_id = u.id
    left join comment c on c.post_id = p.id
) t
where rn = 1

Demo on DB Fiddle

如何在MySQL中根据其最大日期只选择左表的一条记录？

mirror_cat 回答：如何在MySQL中根据其最大日期只选择左表的一条记录？

大家都在问