计算属于一个类别及其子类别的所有帖子

Counting all the posts belonging to a category AND its subcategories(计算属于一个类别及其子类别的所有帖子)

问题描述

我真的很感激能帮助我解决我的问题:

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 - 名称 - 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"; 

问题再次是，如何获得每个类别的总和，包括每个相关子类别的总和?

无法将数据库重构为嵌套集格式，因为我正在维护现有系统.

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

这篇关于计算属于一个类别及其子类别的所有帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！