Shulou(Shulou.com)06/01 Report--

1. with as common table expressions

Similar to VIEW, but does not create objects, WITH AS common table expressions do not create objects, can only be followed by SELECT statements, its role:

1. Implementation of recursive queries (tree structure)

2. Common table expressions can be referenced multiple times in a statement to make them more concise

Non-recursive public expressions

It can be defined column or automatic column and select into effect is similar

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

III. Recursive approach

Connect parts through UNION ALL. An expression created by concatenating itself whit as whose concatenation condition is recursive. You can look down from the root node and from the child node to the parent node. Just reverse the connection conditions. For example, change the condition in the code to t.ID = c.ParentId

with tree as( --0 as Level defines the hierarchy of the tree, starting from 0 select *, 0 as Level from ClassUnis where ParentId is null union all --t.Level + 1 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 recursions with option(maxrecurrence Number). For example, an appeal result with a maximum Level of 2 indicates recursion twice. We set it 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 is here, I hope it helps you.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.