SqlServer无限级树形构建

454luikty

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;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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