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);          }        }      }  

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注