当前位置：文江博客话题详情

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

发布于 2024-08-12 11:07:26 字数 600 浏览 8 评论 0原文

我真的很感谢一些帮助解决我的问题：

我有 2 个 MySQL 表、类别和帖子，布局（简化）如下：

类别：

CATID - name -parent_id

帖子：

PID - 名称 - 类别

我想要做的是获取每个类别的帖子总数，包括子类别中的所有帖子。

现在，我通过执行以下操作来获取每个（顶级）类别（但不是子类别）中的帖子总数：

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

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

将数据库重组为嵌套集格式是不可能的，因为我正在维护现有系统。

感谢您的帮助！

原文

I would really appreciate some help with my problem:

I have 2 MySQL tables, categories and posts, laid out (simplified) like so:

categories:

CATID - name - parent_id

posts:

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.

Thanks for your help!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

吾家有女初长成 2024-08-19 11:07:26

如果类别不是无限嵌套的，您可以一次 JOIN 一层。以下是最多 3 层嵌套的示例：

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

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

回复收藏 0 原文