MySQL订单发布的最新评论或最新发布

一尘不染

MySQL订单发布的最新评论或最新发布

mysql

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

# 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;

我想按最新顺序对帖子进行排序，其中新帖子显然比以前发布的帖子要新，但是具有最新评论的旧帖子也可以视为最新帖子。

第一次尝试

# 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

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

第二次尝试

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

不起作用，因为如果帖子中有多个评论，则 新近度 将匹配第一行的创建值，这是最早的评论。

有没有一种方法可以 按p.id分组（因此，每个帖子仅选择一个副本），但是每个组中的排序是按c.date desc进行的，但是查询顺序by是由新近度完成的？我想不出一种方法，而不必在 post上 添加一个 更新的 字段，每当发布回复时我都会写入该字段… __

谢谢！

阅读 141

2020-05-17

共1个答案

一尘不染

应该这样做：

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

如果我们假设评论总是早于帖子，则可以简化以下内容：

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

2020-05-17