MySQL按最新评论或最后发布的帖子排序

如何对帖子进行排序，以便将最近的活动排在最前面?

# Schema 不包括所有信息，包括 FKs创建表格帖子(id int 无符号主键 auto_increment，消息文本，创建日期时间)引擎=InnoDb;创建表注释(id int 无符号主键 auto_increment，post_id int 无符号，消息文本，创建日期时间)引擎=InnoDb;

我想按最近的帖子排序，其中新帖子显然比以前发布的帖子更新，但带有最近评论的旧帖子仍属于最近的帖子.

第一次尝试

# 在这个例子中为了简单起见选择'*'选择 *从后 p在 c.post_id = p.id 上左加入评论 c按 p.id 分组按 c.created desc、p.created desc 排序

这不起作用，因为新帖子排在带有评论的旧帖子之后.

第二次尝试

select *, if(c.id is null, p.created, c.created) as recency从后 p在 c.post_id = p.id 上左加入评论 c按 p.id 分组按近期降序排序

不起作用，因为如果帖子有多个评论，recency 将匹配第一行的 created 值，这是最早的评论.

*有没有办法按p.id分组(所以每个post只选一份)，但是每组内的排序是按c.date desc，但是查询顺序by 是由最近完成的吗?如果不向 post 添加 updated 字段，我想不出一种方法来做到这一点，每当发布回复时我都会写信给它...>

谢谢！

解决方案

应该这样做:

SELECT p.id发件人LEFT JOIN 评论 c 关于 c.post_id = p.id按 p.id 分组ORDER BY COALESCE(GREATEST(p.created, MAX(c.created)), p.created) DESC

如果我们假设评论总是比帖子早，我们可以简化:

SELECT p.id发件人LEFT JOIN 评论 c 关于 c.post_id = p.id按 p.id 分组ORDER BY COALESCE(MAX(c.created), p.created) DESC

How can I sort posts so most recent activity is on top?

# Schema not including all info, including FKs
CREATE TABLE post(
   id int unsigned primary key auto_increment,
   msg text,
   created datetime
)ENGINE=InnoDb;

CREATE TABLE comment(
   id int unsigned primary key auto_increment,
   post_id int unsigned,
   msg text,
   created datetime
)ENGINE=InnoDb;

I want to order posts by most recent, where a new post is obviously more recent than one previously posted, but an old post that has a recent comment associated with it qualifies as more recent still.

1st attempt

# Selecting '*' for simplicity in this example
select *
from post p
left join comment c on c.post_id = p.id
group by p.id
order by c.created desc, p.created desc

This doesn't work because new posts are sorted after old posts with comments.

2nd attempt

select *, if(c.id is null, p.created, c.created) as recency
from post p
left join comment c on c.post_id = p.id
group by p.id
order by recency desc

Doesn't work because if a post has more than one comment, recency will have the created value of the first row matched, which is the oldest comment.

*Is there a way to group by p.id (so only one copy of each post is selected), but the sorting within each group is by c.date desc, but the query order by is done by recency? I can't think of a way to do that without possibly adding a updated field to post, which I'd write to whenever a reply is posted...

Thanks!

解决方案

This should do it:

SELECT p.id
FROM post p
    LEFT JOIN comment c on c.post_id = p.id
GROUP BY p.id
ORDER BY COALESCE(GREATEST(p.created, MAX(c.created)), p.created) DESC

If we assume that a comment is always older than the post, we can simplify:

SELECT p.id
FROM post p
    LEFT JOIN comment c on c.post_id = p.id
GROUP BY p.id
ORDER BY COALESCE(MAX(c.created), p.created) DESC