SqlServer使用公用表表达式（CTE）实现无限级树形构建

sql server 2005开始，我们可以直接通过cte来支持递归查询,cte即公用表表达式

公用表表达式（cte），是一个在查询中定义的临时命名结果集将在from子句中使用它。每个cte仅被定义一次（但在其作用域内可以被引用任意次），并且在该查询生存期间将一直生存。可以使用cte来执行递归操作。

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,
 level
from cte_parent where level <=@level;
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 = new list<logisticscategorytreeentity>();
          category.children.addrange(subcategorylist);
          buildcategory(allcategorylist, category.children);
        }
      }
    }