TSQL - 查询以获取与每个类别和子类别关联的项目数

Question

我有以下表格：

Categories
=================================
CategoryID |   ParentID   | Text 
---------------------------------
     1           NULL       Text
     2            1         Text
     3           NULL       Text
     4            1         Text

Items
=================================
  ItemID  |  CategoryID  | Text
---------------------------------
     1           1         Text
     2           2         Text
     3           4         Text
     4           3         Text

请记住，这不是一个 n 级层次结构，类别只有 2 级，因此没有类别的 ParentID 可以为 2。

我正在寻找的是一种返回类别的方法，并带有一个额外的列，该列显示该类别拥有的项目数量（包括其子类别）。

即我正在寻找一个可以返回类似以下内容的查询（或过程）：

      Categories
============================================
CategoryID |   ParentID   | Text  |  Count
--------------------------------------------
     1           NULL       Text      3
     2            1         Text      1
     3           NULL       Text      1
     4            1         Text      1

我当前获取与类别关联的项目的方法如下（给定类别ID，@CategoryID）：

SELECT * FROM Items
WHERE CategoryID 
IN (SELECT CategoryID FROM Categories where ParentID = @CategoryID or CategoryID = @CategoryID)

我的问题是我不能似乎将其链接到类别本身的选择查询。 这可能非常简单，但我尝试过使用 CTE 和各种 group by 子句的方法，但类别的层次结构似乎使我的逻辑失去了意义。

感谢您的帮助！

编辑：查询还需要考虑没有与其关联的项目的类别

Answer 1

您必须将项目加入类别，并根据类别表中所需的任何字段进行分组：

SELECT  c.CategoryID
,       c.ParentID
,       c.Text
,       count(distinct i.ItemID)
FROM    Categories c
LEFT JOIN
        Categories c2
ON      c.CategoryID = c2.ParentID
LEFT JOIN    
        Items i
ON      i.CategoryID = c.CategoryID
        or i.CategoryID = c2.CategoryID
GROUP BY
        c.CategoryID
,       c.ParentID
,       c.Text