为我正在制作的论坛创建"bump-system“的MySQL查询

Question

我在构造执行以下操作的MySQL查询时遇到了问题：

从threads表中选择所有线程in，按照最近的post (最大的时间戳)的时间戳(按降序)排序，其中threadID等于每个线程的threadID。所以基本上我想遍历这些线程，让MySQL检查数据库，比如说线程0。然后，它检查所有threadID为0的帖子，并根据thread0中帖子的最大时间戳对thread0进行排序。然后，它对thread1、thread2等重复此操作，并相应地对它们进行排序。

这有可能吗？它是为了创建论坛的"bump-system“效果，在论坛中，最近活跃的帖子不断地被推到列表的顶部，直到该帖子消亡，然后它下降到底部。我曾经使用一种不同的实现，我将lastActivity时间戳存储在线程表中，并在将新的帖子提交到线程中时更新它，但这种查询将使事情变得更有效率。

非常感谢!这里有两个相关的表:线程和帖子。帖子有一个threadID字段来存储它所属的线程的ID，它还有一个时间戳字段。线程有一个对应于帖子的threadID的字段threadID。

Answer 1

以下是我过去在MySql上用过的方法。如果希望在查询中包含有关每个线程的更多信息，则必须将列添加到SELECT和GROUP BY中。

select thread.threadID, max(comments.modifiedOn) as threadUpdated 
   from thread inner join comments on thread.threadID = comments.threadID 
   group by 1 
   order by 2 desc;

这个查询服务于您的主要请求，它是我最近的评论中排序的一个线程列表。它不会按原样返回没有注释的线程，为此，您需要将连接更改为外部连接。

Answer 2

SELECT  *
FROM    threads t
LEFT JOIN
        posts p
ON      p.id =
        (
        SELECT  p.id
        FROM    posts pi
        WHERE   pi.threadID = t.threadID
        ORDER BY
                pi.timestamp DESC
        LIMIT 1
        )

在posts (threadID, timestamp)上建立索引将极大地改进此查询。

请注意，与GROUP BY解决方案不同的是，此查询还会选择posts中的所有字段，即使您在最新的posts.timestamp上有重复的字段，此查询也会起作用。

Answer 3

SELECT t.*, p1.`timestamp`
FROM threads t 
  JOIN posts p1 ON (p1.threadid = t.id)
  LEFT JOIN posts p2 ON (p2.threadid = t.id 
    AND p1.`timestamp` < p2.`timestamp`)
WHERE p2.threadid IS NULL
ORDER BY p1.`timestamp` DESC;