SQL server queries tree structure through with as method

Time:2020-9-23

1、 With as common table expression

It is similar to view, but does not create an object. With as common table expression does not create an object and can only be followed by a select statement

1. Realize recursive query (tree structure)

2. The common table expression can be referenced multiple times in a statement to make it more concise

2、 Non recursive common expressions

It can be defined column or automatic column, which is similar to select into

--Specify the column
with withTmp1 (code,cName)
as
(
 select id,Name from ClassUnis
)
select * from withTmp1
--Automatic column
with withTmp2 
as
(
 select * from ClassUnis
 where Author = 'system'
)
select * from withTmp2

3、 Recursive way

Connect parts through union all. When an expression is created by linking its own white as, its join condition is a recursive condition. You can search from the root node down, from the child node to the parent node. Just invert the connection conditions. For example, the condition in the code can be changed to t.id = c.parentid

with tree as(
 --0 as level defines the level of the tree, starting from 0
 select *,0 as Level 
 from ClassUnis
 where ParentId is null
 union all
 --t. Level + 1 increases every recursion
 select c.*,t.Level + 1 
 from ClassUnis c,tree t
 where c.ParentId = t.ID
 --from ClassUnis c inner join tree t on c.ParentId = t.ID
)
select * from tree where Author not like'%/%'

You can also set the maximum number of recursion through option (maxexecution number). For example, an appeal result with a maximum level of 2 means recursion twice. We set the value to 1


with tree as(
 select *,0 as Level from ClassUnis where ParentId is null
 union all
 select c.*,t.Level + 1 from ClassUnis c,tree t where c.ParentId = t.ID
)
select * from tree where Author not like'%/%' 
option(maxrecursion 1)

Well, this article introduces here, I hope it can help you.