Shulou(Shulou.com)05/31 Report--

SQL Server how to query the tree structure through the with as method, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

1. With as common table expression

Similar to VIEW, but does not create objects, WITH AS common table expressions do not create objects, but can only be followed by the SELECT statement, which serves the following purpose:

1. Implement recursive query (tree structure)

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

Second, non-recursive public expressions

It can be a definition column or an automatic column with the same effect as select into.

-- specify 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

Third, the way of recursion

Connect the part through UNION ALL. By concatenating the expression created by its own whit as, its join condition is a recursive condition. You can look down from the root node and from the child node to the parent node. You just need to reverse 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 hierarchy of the tree, starting with 0 select *, 0 as Level from ClassUnis where ParentId is null union all-- t.Level + 1 increments select c. T.ID. Level + 1 from ClassUnis c t.ID tree t where c.ParentId = t.ID-- from ClassUnis c inner join tree t on c.ParentId = tree) select * from tree where Author not like'%/%'

You can also set the maximum number of recursions through option (maxrecursion 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. Tree where c.ParentId. Level + 1 from ClassUnis cPowerTree t where c.ParentId = t.ID) select * from tree where Author not like'%/%' option (maxrecursion 1) this is the answer to the question about how SQL Server queries the tree structure through the with as method. I hope the above content can be of some help to you, if you still have a lot of doubts to be solved. You can follow the industry information channel for more related knowledge.

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.