mysql - 如何根据MySQL中的最大日期仅选择左表的一条记录？

Question

下面是我的数据库结构

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 中的 MAX(created_at) 获取只有一条 Comment 记录的用户列表，如下所示？

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表，反之亦然!

Answer 1

您可以左连接，使用相关子查询检索当前用户最新帖子的 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

Or, if you are running MySQL 8.0, you can use row_number():

select id, name, comment_text, created_at
from (
    select
        u.id,
        u.name,
        c.text comment_text,
        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