当产品位于子类别中时，为每个类别选择产品计数

Question

这是产品的表结构

PROD_ID     CATEG_ID
1           2
2           21
3           211
4           5
5           51

这是类别的表结构

CATEG_ID    PARENT_CATEG_ID
2           NULL
5           NULL
21          2
211         21
51          5

我在为每个类别(包括嵌套类别(选择产品计数时遇到困难。例如，类别 2 有 1 个产品，类别 21 有 1 个产品，类别211有 1 个产品，并且由于类别 21 和 221 分别是类别 2 的直接/间接祖先，因此类别 2 有 3 个产品。所以我需要一个查询或只是一种方法来获取这样的东西：

CATEG_ID    PARENT_CATEG_ID    PRODUCT_COUNT
2           NULL               3   (including product count for categories 21 and 221)
5           NULL               2   (including product count for category 51)
21          2                  2   (including product count for category 221)
211         21                 1   (no category ancestor, only product count for self)
51          5                  1   (no category ancestor, only product count for self) 

是否可以仅使用 SQL，或者我需要添加一些 PHP？

Answer 1

以下操作应该可以：

with recursive cat as (
  select categ_id, 
         parent_categ_id,
         categ_id as root_category, 
         1 as level
  from categories
  where parent_categ_id is null
  union all
  select c.categ_id,
         c.parent_categ_id,
         p.root_category,
         p.level + 1
  from categories c
    join cat as p on p.categ_id = c.parent_categ_id
)
select c.categ_id, 
       p.prod_id,
       (select count(*) from cat c2 where c2.level >= c.level and c2.root_category = c.root_category) as cnt
from cat c
  left join products p on p.categ_id = c.categ_id
;

递归查询首先构建整个类别树。它返回每个类别的根类别以及特定根类别的子树内类别的嵌套级别。CTE 本身返回以下内容：

categ_id |parent_categ_id |root_category |水平---------+-----------------+---------------+------       2 |         (空( |            2 |    1      21 |              2 |            2 |    2     211 |             21 |            2 |    3       5 |         (空( |            5 |    1      51 |              5 |            5 |    2

然后，这用于连接产品表，并对同一根类别(即count(p.prod_id) over (partition by c.root_category order by level desc)部分(中包含的产品进行运行总和。所以完整查询的结果是这样的：

categ_id |prod_id |product_count---------+---------+--------------       2 |      1 |            3      21 |      2 |            2     211 |      3 |            1       5 |      4 |            2      51 |      5 |            1

SQLFiddle： http://sqlfiddle.com/#!15/d6261/15

Answer 2

在这里，我们检查 c1.categ 是否具有从自身开始的递归查询，该查询通过在它下面构建树来获取所有子类别 ID。如果是这样，则它还将产品计入子类别

select c1.categ_id,c1.parent_categ_id,count(prods.prod_id)
as product_count from categ c1
join prods on prods.categ_id=c1.categ_id or prods.categ_id
in( with recursive tree(id,parent_id)as 
(select categ_id,parent_categ_id from categ 
where categ_id=c1.categ_id  
union all
select cat.categ_id,cat.parent_categ_id from categ cat
join tree on tree.id=cat.parent_categ_id) select id from tree)
group by c1.categ_id,c1.parent_categ_id order by product_count

结果如下

+----------+-----------------+---------------+
| categ_id | parent_categ_id | product_count |
+----------+-----------------+---------------+
|       51 |               5 |             1 |
|      211 |              21 |             1 |
|        5 |            NULL |             2 |
|       21 |               2 |             2 |
|        2 |            NULL |             3 |
+----------+-----------------+---------------+