SqlServer使用公用表表达式（CTE）实现无限级树形构建
SQLServer2005开始，我们可以直接通过CTE来支持递归查询,CTE即公用表表达式
公用表表达式（CTE），是一个在查询中定义的临时命名结果集将在from子句中使用它。每个CTE仅被定义一次（但在其作用域内可以被引用任意次），并且在该查询生存期间将一直生存。可以使用CTE来执行递归操作。
DECLARE@LevelINT=3 ;WITHcte_parent(CategoryID,CategoryName,ParentCategoryID,Level) AS ( SELECTcategory_id,category_name,parent_category_id,1ASLevel FROMTianShenLogistic.dbo.ProductCategoryWITH(NOLOCK) WHEREcategory_idIN ( SELECTcategory_id FROMTianShenLogistic.dbo.ProductCategory WHEREparent_category_id=0 ) UNIONALL SELECTb.category_id,b.category_name,b.parent_category_id,a.Level+1ASLevel FROMTianShenLogistic.dbo.ProductCategoryb INNERJOINcte_parenta ONa.CategoryID=b.parent_category_id ) SELECT CategoryIDASvalue, CategoryNameaslabel, ParentCategoryIDAsparentId, Level FROMcte_parentWHERELevel<=@Level; publicstaticListGetLogisticsCategoryByParent(int?level) { if(level<1)returnnull; vardataResult=CategoryDA.GetLogisticsCategoryByParent(level); varfirstlevel=dataResult.Where(d=>d.level==1).ToList(); BuildCategory(dataResult,firstlevel); returnfirstlevel; } privatestaticvoidBuildCategory(List allCategoryList,List categoryList) { foreach(varcategoryincategoryList) { varsubCategoryList=allCategoryList.Where(c=>c.parentId==category.value).ToList(); if(subCategoryList.Count>0) { if(category.children==null)category.children=newList (); category.children.AddRange(subCategoryList); BuildCategory(allCategoryList,category.children); } } }