MySQL 在具有多个连接的组内排序

Question

我正在尝试使用下表： 用户（ID、姓名） 主题（id、名称、slug） 帖子（id、标题、内容、topicid、authorid、日期时间、slug） 回复（id、authorid、threadid、内容、日期时间）

我想创建一个按最新回复（如果没有回复则按发布日期）排序的帖子列表。每个列表应该包含：帖子标题、回复数、发帖日期、最近回复日期、作者姓名、最后回复者姓名等。

我可以获得除最新回复的日期和作者。日期可以通过 MAX(replies.datetime) 完成，但我不知道如何获取最新的作者。

我的最后一次尝试是尝试使用排除连接：

select posts.id, posts.title, posts.authorid, posts.topicid, posts.content, posts.datetime, count(replies.id) as replies, r2.datetime, replies.datetime, GREATEST(posts.datetime, coalesce(max(replies.datetime), posts.datetime)) as latesttime, users.name as author, commenters.name as commenter, r2.id 
from posts 
left join replies on replies.threadid = posts.id 
left join users on users.id = posts.authorid 
left join users commenters on commenters.id = replies.authorid
left join replies as r2 on replies.id = r2.id and replies.datetime < r2.datetime 
where r2.id is null 
group by posts.id 
order by latesttime DESC, replies.datetime DESC 
limit 20;

不幸的是，这仍然无法检索最新的评论作者。有什么想法吗？

Answer 1

您必须首先从给定线程的最大 ID 的最内部查询开始，并将其回复 ID 加入到原始回复中以获得一个实例...从中，您可以获得回复作者的信息。然后，根据需要获取原始发布信息。

SELECT 
      p.id,
      p.title,
      p.content,
      p.topicid,
      p.authorid,
      p.datetime as postdate,
      p.slug,
      postUser.Name as PostAuthor,
      coalesce( MaxReplyUser.NumReplies, 0 ) NumReplies,
      coalesce( MaxReplyUser.name, '' ) ReplyUser,
      coalesce( MaxReplyUser.AuthorID, 0 ) ReplyAuthor,
      coalesce( MaxReplyUser.ThreadID, 0 ) ReplyThread,
      coalesce( MaxReplyUser.DateTime, '' ) ReplyDateTime,
      coalesce( MaxReplyUser.Content, '' ) ReplyContent
   from 
      Posts p
         left join 
            ( SELECT 
                     u1.name,
                     r1.authorid,
                     r1.threadid,
                     r1.datetime,
                     r1.content,
                     MaxReplies.NumReplies
                  from 
                    ( SELECT 
                            threadid,
                            COUNT(*) as NumReplies,
                            MAX( id ) as MaxReplyID
                         from 
                            replies
                         group by 
                            threadID ) MaxReplies
                       INNER JOIN replies r1
                          ON MaxReplies.MaxReplyID = r1.id
                          INNER JOIN Users u1
                             ON r1.AuthorID = u1.ID ) MaxReplyUser
           ON p.id = MaxReplyUser.threadID
         inner join users postUser
            ON p.authorid = postuser.id
    order by 
       if( MaxReplyUser.threadID IS NULL, p.DateTime, MaxReplyUser.DateTime ) DESC

Answer 2

你能测试一下吗？

SELECT posts.id, posts.title, posts.authorid, posts.topicid, posts.content, posts.datetime, 
(SELECT name FROM users WHERE id = posts.autorid) "Author",
(SELECT COUNT(*) FROM replies WHERE threadid = posts.id) "Replies",
(SELECT MAX(datetime) FROM replies r WHERE threadid = posts.id) "Lastreplytime",
(SELECT (SELECT name FROM users WHERE id = r.authorid LIMIT 1) FROM replies r WHERE threadid = posts.id ORDER BY datetime DESC LIMIT 1)
FROM posts 
ORDER BY Lastreplytime DESC
LIMIT 20;