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

How to use common table expression to realize infinite tree construction in SqlServer, 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.

Since SQL Server 2005, we can support recursive queries directly through CTE, and CTE is the common table expression.

The common table expression (CTE), a temporary named result set defined in the query, will be used in the from clause. Each CTE is defined only once (but can be referenced any number of times within its scope) and will survive during the lifetime of the query. You can use CTE to perform recursive operations.

DECLARE @ Level INT=3 WITH cte_parent (CategoryID,CategoryName,ParentCategoryID,Level) AS (SELECT category_id,category_name,parent_category_id,1 AS Level FROM TianShenLogistic.dbo.ProductCategory WITH (NOLOCK) WHERE category_id IN (SELECT category_id FROM TianShenLogistic.dbo.ProductCategory WHERE parent_category_id=0) UNION ALL SELECT b.paradypaperiddline b.parentroomnameauthorb.Levelroom1 AS Level FROM TianShenLogistic.dbo.ProductCategory b INNER JOIN cte_parent an ON a.CategoryID = b.parent_category_id) SELECT CategoryID AS value, CategoryName as label ParentCategoryID As parentId, LevelFROM cte_parent WHERE Level d.level = 1) .ToList () BuildCategory (dataResult, firstlevel); return firstlevel;} private static void BuildCategory (List allCategoryList, List categoryList) {foreach (var category in categoryList) {var subCategoryList = allCategoryList.Where (c = > c.parentId = = category.value). ToList (); if (subCategoryList.Count > 0) {if (category.children = = null) category.children = new List (); category.children.AddRange (subCategoryList) BuildCategory (allCategoryList, category.children);} this is the answer to the question about how to use common table expressions to build an infinite tree in SqlServer. 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.