使用sql递归计算形成树 [英] Recursive calculation to form a tree using sql

问题描述

我正在处理一个简单的问题并想使用 SQL 解决它.我有 3 个表类别、项目和一个关系表 CategoryItem.我需要返回每个类别的项目数，但扭曲的是类别以父子关系排列，子类别中的项目数应添加到其父类别中的计数中.请考虑下面的示例数据和使用 SQL 的预期结果集.

I am working on a simple problem and wanted to solve it using SQL. I am having 3 tables Category, Item & a relational table CategoryItem. I need to return count of items per category but the twist is Categories are arranged in Parent-Child relationships and the count of items in child categories should be added to the count in its parent Category. Please consider the sample data below and the expected resultset using SQL.

Id  Name    ParentCategoryId
1   Category1   Null
2   Category1.1 1
3   Category2.1 2
4   Category1.2 1
5   Category3.1 3

ID  CateoryId   ItemId
1   5              1
2   4              2
3   5              2
4   3              1
5   2              3
6   1              1
7   3              2

结果:

CategoryNAme     Count
Category1          7
Category1.1        5
Category2.1        4
Category1.2        1
Category3.1        2

我可以在我的业务层中做到这一点，但由于数据的大小，它的性能不是最佳的.我希望如果我能在数据层做到这一点，我就能大大提高性能.

I can do it in my business layer but performance its not optimal because of size of data. I am hoping if I can do it in data layer, I would be able to improve performance greatly.

提前感谢您的回复

推荐答案

您的表格和示例数据

create table #Category(Id int identity(1,1),Name Varchar(255),parentId int)
INSERT INTO #Category(Name,parentId) values
('Category1',null),('Category1.1',1),('Category2.1',2),
('Category1.2',1),('Category3.1',3)

create table #CategoryItem(Id int identity(1,1),categoryId int,itemId int)
INSERT INTO #CategoryItem(categoryId,itemId) values
(5,1),(4,2),(5,2),(3,1),(2,3),(1,1),(3,2)

create table #Item(Id int identity(1,1),Name varchar(255))
INSERT INTO #Item(Name) values('item1'),('item2'),('item3')

通过递归检查父级的所有子级公共表表达式

;WITH CategorySearch(ID, parentId) AS
(
SELECT ID, ID AS ParentId FROM #Category
UNION ALL
SELECT CT.Id,CS.parentId  FROM #Category CT
INNER JOIN CategorySearch CS ON CT.ParentId = CS.ID
)
select * from CategorySearch order by 1,2

输出:针对父级的所有子记录

Output: All child records against parent

ID  parentId
1   1
2   1
3   1
4   1
5   1
2   2
3   2
5   2
3   3
5   3
4   4
5   5

最终查询结果，计算类别及其子类别的所有项目.

Final query for your result, count all items for category and its children categories.

;WITH CategorySearch(ID, parentId) AS
(
SELECT ID, ID AS ParentId FROM #Category
UNION ALL
SELECT CT.Id,CS.parentId  FROM #Category CT
INNER JOIN CategorySearch CS ON CT.ParentId = CS.ID
)
SELECT CA.Name AS CategoryName,count(itemId) CountItem
FROM #Category CA 
INNER JOIN CategorySearch CS ON CS.ParentId = CA.id
INNER JOIN #CategoryItem MI ON MI.CategoryId =CS.ID
GROUP BY CA.Name

输出:

CategoryName    CountItem
Category1           7
Category1.1         5
Category1.2         1
Category2.1         4
Category3.1         2

这篇关于使用sql递归计算形成树的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！