这可能不是最有效的方法，但它是有效的。递归CTE构建给定类别的所有后代的列表（从CTE中的第一个查询中删除WHERE子句以构建所有类别）：

WITH RECURSIVE hierarchy (id, descendant, top_level_id) AS (
    SELECT id, FALSE, id
    FROM post_categories
    WHERE id = 2

    UNION ALL

    SELECT pc.id, TRUE, h.top_level_id
    FROM hierarchy h
    JOIN post_categories pc ON h.id = pc.parent_id
)
SELECT
    pc.*,
    SUM(descendant) AS num_categories,
    SUM((SELECT COUNT(*) FROM posts WHERE post_category_id = h.id)) AS num_posts
FROM post_categories pc
JOIN hierarchy h ON h.top_level_id = pc.id
WHERE pc.id = 2
GROUP BY pc.id;

对于您的问题中提供的示例数据，它输出：
| 身份证|标题|父ID|num_categories|员额数|
| --------------|--------------|--------------|--------------|--------------|
| 二|车辆|1|二|四|
如果对所有类别运行，则应预先聚合类别计数，因为它们将被多次要求：

WITH RECURSIVE hierarchy (id, descendant, top_level_id) AS (
    SELECT id, FALSE, id
    FROM post_categories

    UNION ALL

    SELECT pc.id, TRUE, h.top_level_id
    FROM hierarchy h
    JOIN post_categories pc ON h.id = pc.parent_id
),
cat_counts (id, cnt) AS (
    SELECT pc.id, COUNT(p.id)
    FROM post_categories pc
    LEFT JOIN posts p ON pc.id = p.post_category_id
    GROUP BY pc.id
)
SELECT pc.*, SUM(descendant) AS categories, SUM(cc.cnt) AS num_posts
FROM post_categories pc
JOIN hierarchy h ON h.top_level_id = pc.id
JOIN cat_counts cc ON h.id = cc.id
GROUP BY pc.id;

其输出：
| 身份证|标题|父ID|分类|员额数|
| --------------|--------------|--------------|--------------|--------------|
| 1|机器|空|四|四|
| 二|车辆|1|二|四|
| 三|工业领域|1|0|0|
| 四|公路汽车|二|0|二|
| 五|轨道汽车|二|0|二|
这里有一个db<>fiddle

我有一对夫妇的问题以上，将有助于完善这个答案，如果它不是你正在寻找的。

def post_and_cat_count(title)
  
  lookup_category = PostCategory.find_by(title: title).id
  child_categories = PostCategory.where(parent_id: lookup_category)

  post_count = Post.where(post_category_id: lookup_category).count + 
  Post.where(post_category_id: child_categories).count

  {posts: post_count, categories: (child_categories.length + 1)}

end

display_counts = post_and_cat_count('Vehicles')
#=> {posts: 4, categories: 3}

puts "Post Category Vehicles (Contains #{display_counts[:posts]} posts in #{display_counts[:categories]} categories)
#=> 'Post Category Vehicles (Contains 4 posts in 3 categories)'

我想这就是你要找的？如果你需要更递归的东西，那么你需要扩展你的问题并给予一个递归的例子。我的意思是一个方法会调用自己并用return子句打破无限循环的情况。