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.