mysql - 选择不同的以删除重复的行？

Question

我有一个带有帖子和评论表的论坛。我想按最近的评论排序:

select distinct(p.id)
    ,p.title
    ,c.id 
from Posts as p
    ,Comments as c 
where c.post_id = p.id 
order by c.id DESC 
LIMIT 50;

但是每条评论我都会得到一行。我知道我想循环浏览最近的评论并获取前 50 个独特的帖子。我只是无法将其转换为 SQL。

Answer 1

这是一个没有子查询的解决方案:

SELECT p.id, p.title, MAX(c.id) AS comment_id
FROM Posts AS p
JOIN Comments AS c 
  ON c.post_id = p.id
GROUP BY p.id
ORDER by comment_id DESC 
LIMIT 50

尽管有子查询，但这种方式可能会更快一些并且更具可扩展性，因为它可以在 limit 子句上进行优化:

SELECT p.id, p.title, MAX(c.id) AS comment_id
FROM Posts p
JOIN (SELECT DISTINCT c.post_id FROM Comments c ORDER BY c.id DESC LIMIT 50) t
ON t.post_id = p.id
JOIN Comments c
  ON c.post_id = p.id
GROUP BY p.id
ORDER BY comment_id DESC

确保 Comments(post_id) 上有索引。