所以，我正在写一个小论坛，我想列出以下内容
- 线程主题
- 启动线程的用户名
- 启动日期
- 最后一个在线程中写文章的人的用户名
- 最后发文日期
我有三个表。
账户+---------------+
| id | username |
|---------------+
| 1 | blargh |
| 2 | hest |
+---------------+
+----+-------+------+---------+
| id | topic | user | thedate |
+----+-------+------+---------+
| 5 | Yarr | 1 | bleh |
+-------------------+---------+
+----+---------+------+---------+--------+
| id | content | user | thedate | thread |
+----+---------+------+---------+--------+
| 8 | aaaa | 1 | aadate | 5 |
+----+---------+------+---------+--------+
| 9 | bbbb | 2 | bbdate | 5 |
+----+---------+------+---------+--------+
我想要什么:
+----+-------+----------+---------+--------------------+----------------+
| id | topic | username | thedate | last_post_username | last_post_date |
+----+-------+----------+---------+--------------------+----------------+
| 5 | Yarr | blarg | bleh | hest | bbdate |
+----+-------+----------+---------+--------------------+----------------+
到目前为止我得到的是:
SELECT
forum_threads.id AS id,
forum_threads.topic AS topic,
forum_threads.time AS time,
accounts.username AS username,
Max(forum_posts.id) AS latest_post_id,
forum_posts.`user` AS `user`,
forum_posts.timeposted AS last_post_time
FROM
((forum_threads
JOIN forum_posts ON ((forum_posts.thread = forum_threads.id)))
JOIN accounts ON ((forum_threads.`user` = accounts.id)))
我似乎找不到上一张海报的用户名和时间
首先——我在你的模式中没有看到任何将帖子链接到线程的内容。我的答案是假设posts中有一个额外的列称为threadid。
我看到这个问题的最常见的解决方案是跟踪threads表中最近的帖子的ID(可能是用户ID和用户名)。如果您只需要ID:
SELECT threadid, MAX(id) FROM posts WHERE <...> GROUP BY threadid
但是没有有效的方法从该查询中获得相关的时间或用户ID。我能得到的最接近的是这一堆:
SELECT threadid, id, user, username, thedate FROM posts
WHERE posts.id IN (
SELECT threadid, MAX(id) FROM posts WHERE <...> GROUP BY threadid
)
这在MySQL上是非常低效的——优化器在GROUP BY的子查询上完全崩溃了。(在一个少于100个线程的测试数据库中，查询大约需要300毫秒。)只要咬紧牙关，通过存储线程中最新帖子的信息来非规范化数据库，一切都会好起来的。