CTE 递归

　　　　　　　　　　　　　　CTE (Common Table Expression)

他的用法很简单， 形如 with cte_name (cols)

 　　　　　　　　　　　as ( sql expression) 

定义如上，使用的时候  可以当做可数据结果集使用  select * from cte_name 

例如 ：

 1 with DBset 
 2 as
 3 (
 4 select OID as 订单号,
 5         case  
 6         when Sale > 0 then Sale
 7         else 0
 8         end as 收入,
 9         case  
10         when Sale < 0 then ABS(Sale)
11         else 0 
12         end as 支出
13         from [Order]
14 )
15 
16 select * from DBSet where 支出=0

　　结果： 

　　   

  一条cte 表达式 可以在 一段sql表达式重复引用，但是不能在两段 sql 表达式中 重复引用 

 一段sql表达式重复引用： 

select * from DBSet where 支出=0
union all 
select * from DBSet where 收入=0

 结果： 

不能在两段 sql 表达式中 重复引用：

select * from DBSet where 支出=0
union all 
select * from DBSet where 收入=0
go
select * from DBSet

结果： 

 

CTE 递归： 

说到 CTE递归，首先看 个结果集合  表ColNews :

　　　　　　　　　　　　　　　　　　　　　　　　　　---------我想让左边表查询显示出右边的需求（也就是显示子节点的层级）------------->

   　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

 

 我们看下 没有 CTE 表达式情况下，怎么实现，  

 1 -- 当没有 CTE，实现方式
 2 create table #item 
 3 (
 4     Id int ,
 5     tLevel int,    
 6 )
 7 
 8 create table #list
 9 (
10     Id int,
11     Name nvarchar(50),
12     ParentId int,
13     tLevel int
14 )
15 
16 insert into #item 
17 select Id,0 from ColNews 
18 where ParentId =0
19 insert into #list 
20 select C.Id,C.Name,C.ParentId,#item.tLevel from ColNews as C 
21 inner join #item 
22 on C.Id = #item.Id 
23 --
24 declare @curID int,@curLevel int
25 set @curID =1;
26 set @curLevel = 0;
27 -- 
28 while @curID > 0 
29 begin 
30 
31 insert into #item
32 select C.Id,#item.tLevel+1 from ColNews as C ,#item 
33 where C.ParentId = #item.Id
34 
35 delete from #item 
36 where #item.tLevel = @curLevel 
37 
38 select @curID = COUNT(Id) from #item -- 当 #item 表没有数据，循环中止 
39 
40 set @curLevel = @curLevel +1 -- 把当前级别往下推
41 
42 insert into #list 
43 select C.Id,C.Name,C.ParentId,#item.tLevel from ColNews as C 
44 inner join #item 
45 on C.Id = #item.Id 
46 end

View Code

可以看到 借助于两个临时表 ，而且代码很多，复杂，不易懂； 现在有了CTE，

看看 CTE 方式 ：

 1 with ColCte (Id,Name,ParentId,tLevel)
 2 as 
 3 (
 4     select *,0 as tLevel from ColNews 
 5     where ColNews.ParentId = 0
 6     union all 
 7     select CN.*,CC.tLevel + 1 as tLevel from ColNews as CN 
 8     inner join ColCte as CC 
 9     on CN.ParentId = CC.Id 
10 )
11 
12 select * from ColCte
13 Option(MAXRECURSION 3)

短短不过 10行的代码量，也容易读懂，

 

转载于:https://www.cnblogs.com/rosesBlogs/p/4605299.html