PHP/MySQL:单查询与多调用

Question

考虑到我的情况，什么是更优雅的解决方案？以下是查询：

SELECT id, forum, des, num_topics, num_posts, last_post,
(SELECT topic_id FROM posts WHERE id = last_post) AS topic_id,
(SELECT author_id FROM posts WHERE id = last_post) AS author_id,
(SELECT topic FROM topics WHERE id = (SELECT topic_id FROM posts WHERE id = last_post)) AS topic,
(SELECT username FROM users WHERE id = (SELECT author_id FROM posts WHERE id = last_post)) AS username
FROM
(SELECT 
forums.id, forums.forum, forums.cat_id, forums.des,
(SELECT COUNT(id) FROM topics WHERE forum_id = forums.id) AS num_topics, 
COUNT(posts.id) FROM posts AS num_posts,
MAX(posts.id) AS last_post
FROM forums
LEFT JOIN topics ON topics.forum_id = forums.id
LEFT JOIN posts ON posts.topic_id = topics.id
GROUP BY forums.id) AS t1

它基本上选择论坛名称、描述、每个论坛的主题和帖子数量，以及从特定论坛中最后一个帖子id派生的最后一个活动线程。一切都正常，只是它看起来很难看，尤其是最上面一批嵌套的SELECT，这似乎是多余的，但我看不出有任何方法可以在那里使用JOIN，除非我模仿了另一个表(t2(，我真的不知道该怎么做，也不知道在这一点上是否可能。所以我想知道，它真的比使用单独的SQL调用来获取最后一个活动线程数据更高效吗？像这样：

SELECT topics.topic_id, topics.topic, posts.author_id, users.username 
FROM posts
JOIN topics ON topics.id = posts.topic_id
JOIN users ON users.id = posts.author_id
WHERE posts.id = $last_post

正如您所看到的，这里的链接是$last_post，它是我从上面的查询中获得的，对我来说，将其拆分似乎比在单个查询中使用一堆嵌套的SELECT更优雅。但是，如果有任何方法可以将后一个查询集成到前一个查询中，请以某种方式告诉我。

Answer 1

网络/数据库连接通常是一个很大的开销。执行单个查询可以避免执行两个查询的开销。

我会把它写如下，边写边汇总，然后留下来查看结果，以获取"最新"帖子的详细信息。

但是，请注意，MAX(id)并不总是保证是最新的一行。交易和人工干预可能会造成混乱。然而，由于你不是在谈论财务后果，我认为这已经足够接近了。

SELECT
forums.*,
topics.num_topics,
topics.num_posts,
latest_post.topic_id    AS latest_post_topic_id,
post_topic.topic        AS latest_post_topic,
latest_post.author_id   AS latest_post_author_id,
post_user.username      AS latest_post_author_username 
FROM
forums
LEFT JOIN
(
SELECT
topics.forum_id,
COUNT(id)              AS num_topics,
SUM(posts.num_posts)   AS num_posts,
MAX(posts.max_post_id) AS max_post_id
FROM
topics
LEFT JOIN
(
SELECT
topic_id,
COUNT(*)   AS num_posts,
MAX(id)    AS max_post_id
FROM
posts
GROUP BY
topic_id
)
AS posts
ON posts.topic_id = topics.id
GROUP BY
topics.forum_id
)
AS topics
ON topics.forum_id = forums.id
LEFT JOIN
posts  AS latest_post
ON latest_post.id = topics.max_post_id
LEFT JOIN
topics  AS post_topic
ON post_topic.id  = latest_post.topic_id
LEFT JOIN
users   AS post_user
ON post_user.id   = latest_post.author_id

编辑：

你在评论中说你不知道这个东西是干什么的。

所以，把它分解，从最内部的查询开始。。。

(
SELECT
topic_id,
COUNT(*)   AS num_posts,
MAX(id)    AS max_post_id
FROM
posts
GROUP BY
topic_id
)
AS posts
-- This takes all posts and aggregates to one row per topic
---> counts how many posts there are in the topic
---> finds the 'newest' post id from that topic

然后。。。

(
SELECT
topics.forum_id,
COUNT(id)              AS num_topics,
SUM(posts.num_posts)   AS num_posts,
MAX(posts.max_post_id) AS max_post_id
FROM
topics
LEFT JOIN
(
<sub-query from above>
)
AS posts
ON posts.topic_id = topics.id
GROUP BY
topics.forum_id
)
AS topics
-- This takes all the topics and aggregates them to one row per forum
---> counts the number of topics in the forum
---> sums the number of posts per topic to the number of posts in the forum
---> finds the 'newest' post id across all the topics' newest posts

在这一点上，每个论坛有一行，所以不需要更多的聚合。。。

forums
LEFT JOIN
(
<sub-query from above>
)
AS topics
ON topics.forum_id = forums.id
LEFT JOIN
posts  AS latest_post
ON latest_post.id = topics.max_post_id
LEFT JOIN
topics  AS post_topic
ON post_topic.id  = latest_post.topic_id
LEFT JOIN
users   AS post_user
ON post_user.id   = latest_post.author_id
--> Take all forums
--> LEFT JOIN the summary stats from the sub-query
--> LEFT JOIN the post, topic and user details for the newest post (as found from the sub-query)