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.category_id,b.category_name,b.parent_category_id,a. level+1 as level from tianshenlogistic.dbo.productcategory b inner join cte_parent a on a.categoryid = b.parent_category_id) Select categoryid as value,categoryname as label, parentcategoryid as parentid,levelfrom cte_parent where level <[email Protected];
Public static list<logisticscategorytreeentity> getlogisticscategorybyparent (int? Level) { if (level < 1) return null; var dataresult = categoryda.getlogisticscategorybyparent ( level); var firstlevel = dataresult.where (d => d.level == 1). ToList (); buildcategory (DataResult, firstlevel); return firstlevel; } Private static void buildcategory (List<logisticscategorytreeenTity> allcategorylist, list<logisticscategorytreeentity> 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&nbSp;= new list<logisticscategorytreeentity> (); category.children.addrange ( Subcategorylist); buildcategory (Allcategorylist, category.children); } } }
SQL Server Infinite-level tree-shaped construction