mysql - 选择帖子上的最新评论，没有评论字符串索引的关注者总数

Question

我需要从关注者那里获取帖子，每个帖子的评论总数，每个帖子的最新评论。以下是我的表结构示例

post_table
post_id | post_body | post_author | post_date
   1    |     Hi    |      2      | 1970-01-01 05:30:00

Comment table
comment_id | comment_body | entity_id | comment_date | comment_author
     1     |  Hi comment  |      1    |  1970-01-01  |        1

我有 post_id、comment_id、entity_id 和 date 的索引。

我尝试过以下查询

select    p1.author_id,
          p1.post_id,
          count(comment1.entity_id) as comment_count,
          display_name
from      post as p1
left join comments as comment1 
on        p1.post_id = comment1.entity_id 
left join user_details as u1 
on        p1.post_id = u1.uid
where     p1.author_id in (select user_relation.follower_id 
                           from   user_relation 
                           where follower_id = '6')
group by  p1.post_id

我想获取每个帖子的最新评论的评论正文，而不对评论正文进行分组，因为我不想在大字符串上建立索引。我已经尝试过在网上查找和其他问题。

Answer 1

您可以一次性找到每个帖子的评论数和最新评论 ID，然后将其与帖子和评论表连接起来以获取所需的详细信息:

select p.author_id, p.post_id, c.comment_count, c2.comment_body
from post p
left join (
    select entity_id, count(*) as comment_count, max(comment_id) as max_comment_id
    from comments
    group by entity_id
) c on p.post_id = c.entity_id
left join comments c2 on c2.comment_id = c.max_comment_id;