SqlServer使用公用表表达式(CTE)实现无限级树形构建
作者:BruceAndLee 时间:2024-01-22 00:34:09
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);
}
}
}
来源:http://leelei.blog.51cto.com/856755/1957792
标签:SqlServer,cte
0
投稿
猜你喜欢
Git配置别名简化操作命令方式详解
2022-03-20 03:34:12
详解Python flask的前后端交互
2023-03-19 06:41:05
Python之re操作方法(详解)
2022-05-15 18:38:15
iframe的防插与强插
2009-03-03 12:33:00
妙用Dreamweaver MX共享WPS Office文件
2010-09-05 21:18:00
Java数据库连接池之c3p0简介_动力节点Java学院整理
2024-01-19 18:16:03
Dreamweaver的CSS布局ul和li范例
2009-08-28 12:34:00
利用Python绘制一个可爱的米老鼠
2022-01-29 14:34:30
在python中将list分段并保存为array类型的方法
2023-11-15 10:18:00
SQL Server 2008 R2 超详细安装图文教程
2024-01-24 16:41:12
php如何解决无法上传大于8M的文件问题
2024-06-05 09:39:24
Windows下用py2exe将Python程序打包成exe程序的教程
2021-07-11 23:03:17
Django models.py应用实现过程详解
2021-07-18 11:11:08
d3.js实现简单的网络拓扑图实例代码
2024-05-09 10:19:58
ASP.NET页面间的传值的几种方法
2024-05-11 09:26:52
Next.js应用转换为TypeScript方法demo
2024-05-11 09:36:17
python利用tkinter实现屏保
2022-01-26 05:39:51
SQL Server 定时访问url激活数据同步示例
2024-01-24 11:22:50
Java连接mysql数据库代码实例程序
2024-01-22 03:01:26
Python求一批字符串的最长公共前缀算法示例
2021-03-10 13:40:37