mysql 使用递归SQL对项进行分组和计数

3pvhb19x  于 2023-04-19  发布在  Mysql
关注(0)|答案(1)|浏览(156)

我的数据库中有以下数据。我有3个表Category,Item和ItemCategories。表Category有id,name,parent_crawler_id和crawler_id。Item有id和name。ItemCategories有id,item_id和category_id。
以下是类别的示例数据:

id (pk)      name              parent_crawler_id            crawler_id
1            Fruit and Veg     null                         8899
2            Organic           8899                         88100
3            Green             88100                        88101

以下是项目的示例数据:

id(pk)       name              
1            Grapes
2            Apples

下面是ItemCategories的示例数据

id           item_id (fk)      category_id (fk)
1            1                 2
2            2                 3
3            1                 3
4            2                 2

我想对Category表进行分组,并包含其子表的计数。我想获得以下输出:

name            count
Fruit and Veg   6
Organic         4
Green           2

以下是我的查询

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_crawler_id, crawler_id
  FROM "Category"
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_crawler_id, c.crawler_id
  FROM "Category" c
  JOIN category_tree ct ON c.parent_crawler_id = ct.crawler_id
)
SELECT ct.name, SUM(subquery.count) AS total_count
FROM category_tree ct
LEFT JOIN (
  SELECT ic.category_id, COUNT(DISTINCT ic.item_id) AS COUNT
  FROM "ItemCategories" ic
  GROUP BY ic.category_id
) subquery ON subquery.category_id = ct.id
GROUP BY ct.name
ORDER BY ct.name

我如何使用SQL和递归来实现这一点?

polhcujo

polhcujo1#

您可以使用递归CTE构建路径,然后使用此路径对来自后代行的值求和:

with recursive counted as (
  select c.id cid, max(parent_crawler_id) par, max(crawler_id) chd, count(ic.id) cnt
  from Category c
  left join ItemCategories ic on ic.category_id = c.id
  group by c.id),
rec(cid, par, chd, cnt, pth) as (
  select cid, par, chd, cnt, cast(chd as char(1000)) 
  from counted c where par is null
  union all
  select c.cid, c.par, c.chd, c.cnt, concat(rec.pth, '-', c.chd) 
  from counted c join rec on rec.chd = c.par)
select cid, pth, cnt, 
       (select sum(cnt) from rec r where pth like concat(rec.pth,'%')) summed
from rec

dbfiddle demo
在我的dbfiddle中,我添加了更多的行用于测试目的。

相关问题