根据另一个表从表中选择条目

I am working on a forum with a SQLITE3 backend.

我正在一个有SQLITE3后台的论坛上工作。

My goal is to find the last 20 topics that has the most recent posts beneath them. I tried this:

我的目标是找到最后20个话题，其中包含最近的文章。我试着这样的:

SELECT * FROM Topics INNER JOIN Posts ON Topics.ID = Posts.TopicID ORDER BY Posts.Modified DESC LIMIT 20

This is not exactly what I want as it will repeat the same topic everytime it there is a recent post under it. I just want a topic given only once.

这并不是我想要的，因为每次在它下面有一个最近的帖子时，它都会重复同样的主题。我只想要一个只给一次的题目。

1 个解决方案

#1

You have to get the one latest post for each topic, and then sort by that:

你必须获得每个主题的最新文章，然后按此排序:

SELECT Topics.*,
       (SELECT MAX(Modified)
        FROM Posts
        WHERE TopicID = Topics.ID
       ) AS LastModified
FROM Topics
ORDER BY LastModified DESC
LIMIT 20

The same can be done with a join:

同样的事情也可以用join来完成:

SELECT Topics.*,
       MAX(Posts.Modified)
FROM Topics
JOIN Posts ON Topics.ID = Posts.TopicID
GROUP BY Topics.ID
ORDER BY MAX(Posts.Modified) DESC
LIMIT 20

#1