SQL获取每个类别的计数

Question

考虑以下 2 个表

类别

CatId       CatName         HigerCatId
1           Clothing        0
2           Jackets         1      
3           T-Shirts        1
4           BlueT-Shirts    3
5           White-Jackates  2

如果更高的 HigerCatId 为 0，则意味着我们到达了顶级父节点，我们应该停止

产品

ProductId   CatId
0           3
1           1
2           1
3           2
4           2
5           3
6           4
7           3
8           4

预期的结果是

CatName         ChildCats      ProductsCount   ProductIds(Not needed just to give an idea)
Clothing        2,3,4,5        9               0,1,2,3,4,5,6,7,8
Jackets         5              2               3,4
T-Shirts        4              5               0,5,6,7,8
BlueT-Shirts    none           2               6,8 
White-Jackates  none           0               none

我只需要获取属于某个类别的产品总数，父级的总数应包含其下所有类别的总数。我能够在 c# 中使用递归来完成它，我不知道它是否可以直接从 sql 中实现，我的问题是子类别可以有产品，即使它们有更深的子类别，结果是否可以以完全动态的方式从sql server，无论级别的深度如何，还是我应该坚持从 sql 外部执行？

如果可能的话，你能给我一些想法吗？

当我开始一个时，将奖励 200 赏金。

Answer 1

你需要一个 CTE

WITH cte
     AS
     (
         SELECT CatId    AS ParentCatId,
                CatName,
                CatName  AS ParentName,
                HigherCatId,
                CatId,
                CAST(CatName AS VARCHAR(255)) AS PATH
         FROM   Categories
         UNION ALL 
         SELECT c.CatId    AS ParentCatId,
                cte.CatName,
                c.CatName  AS ParentName,
                c.HigherCatId,
                cte.CatId,
                CAST(cte.path + ', ' + c.CatName AS VARCHAR(255)) AS PATH
         FROM   cte
                INNER JOIN Categories c
                     ON  c.CatId = cte.HigherCatId
     )

SELECT c.parentName,
       COUNT(p.ProductId) AS ProductsCount
FROM   cte c
       LEFT OUTER JOIN products p
            ON  c.CatId = p.CatId
GROUP BY
       c.ParentCatId,
       c.ParentName
ORDER BY
       c.ParentCatId

有一些额外的列用于调试目的，您可以在最终代码中删除它们。

查看 SQL Fiddle您可以在其中查看结果和一些中间输出。