当产品在子类别中时，选择每个类别的产品数量

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

在这里，我们检查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 |
+----------+-----------------+---------------+

Answer 2

下面应该可以做到:

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 | level
---------+-----------------+---------------+------
       2 |          (null) |             2 |     1
      21 |               2 |             2 |     2
     211 |              21 |             2 |     3
       5 |          (null) |             5 |     1
      51 |               5 |             5 |     2

然后，这用于连接产品表并对包含在同一根类别中的产品进行运行和（即count（p.prod_id）over（c.root分区_category级别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