我目前正在(尝试)开发一个论坛。现在我遇到了一个小障碍。我的数据库中有四个表;"类别"、"论坛"、"主题"one_answers"回复"。他们都有一个id,一个父id和一个名字。
我现在的问题是我应该如何存储和获得每个类别和论坛的主题和回复数量。一开始我想到了保存每个类别和论坛中的所有主题和回复,以及保存每个主题中的所有回复。
但这是我宁愿尽量避免看到的,因为我必须对每个回复进行1次插入和3次更新;1到类别,1到论坛,1到主题,最后是回复本身,一个插入。
因此,我想是否有可能(以及如何)使用COUNT、GROUP BY和INNER JOIN 进行查询
SELECT *
FROM `categories`
JOIN (COUNT ( * )
FROM `topics`
GROUP BY `parent_id`
WHERE `parent_id` = `categories`.`id`) as `amount_of_topics`
JOIN (COUNT ( * )
FROM `topics`
GROUP BY `parent_id`
WHERE `parent_id` = `topics`.`id`) as `amount_of_replies`
只是我没有按照我想要的方式工作。
因此,为了获取所有回复并统计特定论坛的回复,连接回复和论坛得到的回复是回复的parent_id属于一个主题,而parent_id则属于一个论坛。
我不确定我是否正确理解你的问题,但如果你只想统计某个论坛的主题和回复数(前提是类别属于某个论坛,而不是相反),你可以按照以下行进行查询:
SELECT forums.ID as forum_id
, forums.name as forum_name
, Count(topics.ID) AS count_of_topics
, Count(replies.ID) AS count_of replies
FROM forums
LEFT JOIN categories ON forums.ID = categories.parent_id
LEFT JOIN topics ON categories.ID = topics.parent_id
LEFT JOIN replies ON topics.ID = replies.parent_id
GROUP BY forums.ID, forums.name, categories.ID, categories.name;
如果你正在寻找主题的计数&按类别答复,使用:
SELECT categories.ID as category_id
, categories.name as category_name
, Count(topics.ID) AS count_of_topics
, Count(replies.ID) AS count_of replies
FROM categories
LEFT JOIN topics ON categories.ID = topics.parent_id
LEFT JOIN replies ON topics.ID = replies.parent_id
GROUP BY forums.ID, forums.name, categories.ID, categories.name;
希望这会有所帮助,但如果这不是你想要实现的,请在你的问题中添加精确性。
我想这就是您想要的查询:
SELECT categories.*, `amount_of_topics`, `amount_of_replies`
FROM `categories` JOIN
(select `parent_id`, COUNT ( * ) as `amount_of_topics`
FROM `topics`
GROUP BY `parent_id`
) tp
on tp.`parent_id` = `categories`.`id`) JOIN
(select `topics`.`id`) , COUNT ( * ) as `amount_of_replies`
FROM `topics`
GROUP BY `topics`.`id`
) ti
on tp.`parent_id` = ti.`id`
这个想法是为您想要的每个主题组创建单独的子查询,然后将它们连接在一起。