SqlServer使用公用表表达式实现无限级树形构建_

2019-11-23 23:08 来源:未知

伟德国际官网1946,公用表表达式简介:

SQL Server 2005开始,我们可以直接通过CTE来支持递归查询,CTE即公用表表达式

with cte as(
select bianma,fjbm from #tree where chkDisabled='true'
union all 
select t.bianma,t.fjbm from cte c inner join #tree t on c.fjbm=t.bianma
)
select * from cte;

公用表表达式 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。与派生表的不同之处在于,公用表表达式 具有一个重要的优点,那就是能够引用其自身,从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

公用表表达式,是一个在查询中定义的临时命名结果集将在from子句中使用它。每个CTE仅被定义一次,并且在该查询生存期间将一直生存。可以使用CTE来执行递归操作。

 

下面先创建一个表,并插入一些数据:

DECLARE @Level INT=3;WITH cte_parent(CategoryID,CategoryName,ParentCategoryID,Level)AS( SELECT category_id,category_name,parent_category_id,1 AS Level FROM TianShenLogistic.dbo.ProductCategory WITH WHERE category_id IN ( SELECT category_id FROM TianShenLogistic.dbo.ProductCategory WHERE parent_category_id=0 ) UNION ALL SELECT b.category_id,b.category_name,b.parent_category_id,a.Level 1 AS Level FROM TianShenLogistic.dbo.ProductCategory b INNER JOIN cte_parent a ON a.CategoryID = b.parent_category_id)SELECT CategoryID AS value, CategoryName as label, ParentCategoryID As parentId, LevelFROM cte_parent WHERE Level <=@Level;public static List GetLogisticsCategoryByParent { if  return null; var dataResult = CategoryDA.GetLogisticsCategoryByParent; var firstlevel = dataResult.Where.ToList(); BuildCategory(dataResult, firstlevel); return firstlevel; } private static void BuildCategory(List allCategoryList, List categoryList) { foreach (var category in categoryList) { var subCategoryList = allCategoryList.Where(c => c.parentId == category.value).ToList(); if (subCategoryList.Count > 0) { if (category.children == null) category.children = new List(); category.children.AddRange; BuildCategory(allCategoryList, category.children); } } }
create table Role_CTE( Id int not null, Name nvarchar not null, ParentId int not null )insert into Role_CTEselect '1','超级管理员','0' union select '2','管理员A','1' union select '3','管理员B','2' union select '4','会员AA','2' union select '5','会员AB','2' union select '6','会员BA','3' union select '7','会员BB','3' union select '8','用户AAA','4' union select '9','用户BBA','7' -- 创建一个复合聚集索引create clustered index Clu_Role_CTE_Indexon Role_CTEwith( pad_index=on, fillfactor=50, drop_existing=off, statistics_norecompute=on)select * from Role_CTE

查找指定节点的所有子孙节点:

使用普通 sql 语句实现:

declare @level intdeclare @node intdeclare @ResTab table( node int not null, lv int not null )set @level=0 -- 表示初始的等级set @node=3 --表示初始的节点ID,即从指定的哪个节点开始查找insert into @ResTab -- 为表变量插入初始的数据select Id,@level from Role_CTE where Id=@nodewhilebegin set @level=@level 1 insert into @ResTab select b.Id,@level from @ResTab a join Role_CTE b on a.node=b.ParentId and lv=@level-1 -- join 等于 inner join和自连接endselect a.node,b.Name,a.lv from @ResTab a left join Role_CTE b on a.node=b.Id

以上是根据指定节点ID,查找父节点ID等于指定的节点ID,如果有就插入,并继续循环。

PS:lv=@level-1 是重点,不然会进入死循环,作用就是限制只插入一次。

如果需要限制循环的次数,即递归的层数,那么只需要在 while 条件里面添加一个限制即可。如下:

declare @level intdeclare @node intdeclare @num intdeclare @ResTab table( node int not null, lv int not null )set @level=0 -- 表示初始的等级set @node=3 --表示初始的节点ID,即从指定的哪个节点开始查找set @num=1 -- 指定递归层级,即循环的次数insert into @ResTab -- 为表变量插入初始的数据select Id,@level from Role_CTE where Id=@nodewhile(@@ROWCOUNT>0 and @level当然,如果指定了循环次数,就可以不用 while 判断语句的 @@rowcount>0 了。使用 SQL CTE 实现:declare @node int set @node=3;with temp_cteas( select Id,Name,0 lv -- 查询出“根节点”,即指定的起始节点 from Role_CTE where Id=@node union all select b.Id,b.Name,a.lv 1 from temp_cte a join Role_CTE b on a.Id=b.ParentId)select * from temp_cte

使用 CTE 控制递归的层数,与上面类似。如下:

declare @node int declare @num intset @node=3;set @num=1;with temp_cteas( select Id,Name,0 lv -- 查询出“根节点”,即指定的起始节点 from Role_CTE where Id=@node union all select b.Id,b.Name,a.lv 1 from temp_cte a join Role_CTE b on a.Id=b.ParentId and a.lv查找指定节点的所有祖先节点:使用普通 sql 语句实现:declare @level intdeclare @node intdeclare @num intdeclare @ResTab table( node int not null, lv int not null )set @level=0 -- 表示初始的等级set @node=8 --表示初始的节点ID,即从指定的哪个节点开始查找set @num=2 -- 指定递归层级,即循环的次数while(@level<=@num and @node is not null) -- 如果为空就表示没有查到父级了begin insert into @ResTab select @node,@level set @level=@level 1 select @node=ParentId from Role_CTE where Id=@nodeendselect a.node,b.Name,a.lv from @ResTab a left join Role_CTE b on a.node=b.Id

使用 SQL CTE 实现:

declare @node int declare @num intset @node=8;set @num=2;with temp_cteas( select Id,Name,ParentId,0 lv -- 查询出“根节点”,即指定的起始节点 from Role_CTE where Id=@node union all select b.Id,b.Name,b.ParentId,a.lv 1 from temp_cte a join Role_CTE b on a.ParentId=b.Id and a.lv < @num --控制递归层数)select * from temp_cte

以上所述是小编给大家介绍的SQL Server 公用表表达式实现递归的方法,希望对大家有所帮助,如果大家有任何疑问欢迎给我留言,小编会及时回复大家的,在此也非常感谢大家对脚本之家网站的支持!

版权声明:本文由19463331韦德国际发布于计算机网络,转载请注明出处:SqlServer使用公用表表达式实现无限级树形构建_