问题描述
我真的很感谢我的问题的一些帮助：
I would really appreciate some help with my problem:
我有2个MySQL表，类别和帖子，布局（简化）如下：
I have 2 MySQL tables, categories and posts, laid out (simplified) like so:
类别：
CATID - name - parent_id
CATID - name - parent_id
帖子：
PID - 名称 - 类别
PID - name - category
我想做的是获得每个类别的帖子总数，包括子类别中的任何帖子。
What I would like to do is get the total amount of posts for each category, including any posts in subcategories.
现在我得到每个类别中的总帖子数顶部级别）类别（但不是子类别）：
Right now I am getting the total number of posts in each (top-level) category (but not subcategories) by doing:
"SELECT c.*, COUNT(p.PID) as postCount
FROM categories AS c LEFT JOIN posts AS p
ON (c.CATID = p.category)
WHERE c.parent='0' GROUP BY c.CATID ORDER BY c.name ASC";
问题再次是，如何获得每个类别的总计
The question once again is, how can I get the sum totals for each category including the totals for each related subcategory?
由于我维护现有系统，因此无法将数据库重组为嵌套集格式。
Restructuring the database to a nested set format is not possible, as I am maintaining an existing system.
感谢您的帮助！
推荐答案
如果类别不是无限嵌套，一次加入一个级别。以下是最多3层嵌套的示例：
If the categories are not nested infinitely, you can JOIN them one level at a time. Here's an example for up to 3 levels of nesting:
SELECT c.name, COUNT(DISTINCT p.PID) as postCount
FROM categories AS c
LEFT JOIN categories AS c2
ON c2.parent = c.catid
LEFT JOIN categories AS c3
ON c3.parent = c2.catid
LEFT JOIN posts AS p
ON c.CATID = p.category
OR c2.CATID = p.category
OR c3.CATID = p.category
WHERE c.parent = '0'
GROUP BY c.CATID, c.name
ORDER BY c.name ASC
这篇关于计算属于类别及其子类别的所有帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！