递归 COUNT 查询 (SQL Server)

Question

我有两个 MS SQL 表：类别、问题。每个问题都被分配到一个类别。一个类别可以有多个子类别。

类别

• ID：bigint (PK)
• 名称：nvarchar(255)
• 接受问题：位
• IdParent：bigint (FK)

问题

• ID：bigint (PK)
• 标题：nvarchar(255) ...
• IdCategory : bigint (FK)

如何递归计算给定类别的所有问题（包括子类别中的问题）。我已经根据几个教程尝试过，但仍然无法弄清楚:(

Answer 1

with /* recursive */ category_tree as (
  select category.id as top_category_id, category.id as category_id
  from category
union all
  select top_category_id, category.id
  from category_tree
       join category on category.idparent = category_tree.category_id
)
select category_tree.top_category_id as category, count(*) as question_count
from category_tree
     join question on question.idcategory = category_tree.category_id
group by category_tree.top_category_id

CTE 构建了每个类别下的子类别列表 - 本质上，它在树中递归并生成（顶级类别、后代类别）的扁平化视图。

初始项（在 union all 之前）选择每个类别，并指示它包含自身 - 然后递归项包括迄今为止找到的类别的所有子类别，并在前一个类别中的所有 Category_id 列时自动停止（不产生结果）迭代是叶类别。

基于此，我们只需将这个扁平化视图连接回问题即可生成一组（顶级类别，问题）行，并基于（顶级类别）进行聚合。