1、既然要谈到sql,数据库表是必须的
2、数据结构
3、获取某个节点的所有子节点
传统的写法(sql2000) 很麻烦,暂且就不写了
来看看CTE
的写法
CREATE PROC sp_getTreeById(@TreeId int) AS
BEGIN
WITH cteTree AS
(SELECT *
FROM TuziTree
WHERE Id = @TreeId --第一个查询作为递归的基点(锚点)
UNION ALL
SELECT TuziTree.* --第二个查询作为递归成员, 下属成员的结果为空时,此递归结束。
FROM cteTree
INNER JOIN TuziTree ON cteTree.Id = TuziTree.ParentId
) SELECT * FROM cteTree
END
试一下啊
exec sp_getTreeById @TreeId=1001
结果
4、使用节点路径来做(每个节点路径都保存自身的路径和所有父节点的路径=自己和所有父节点的关联)
5、既然有个路径
那么查询其所有子节点 只需要 where nodePath like '/1001/%'
了
这样就会简单很多,加上索引。
总结:
- 如果在性能的需要上,我们可以采用按需加载,点击节点时候 才会加载其所有子节点。
- 如果在变化不大的情况下,可以采用缓存 。这样的处理 可以满足很多业务需求。
- 良好的表设计会给后期的开发以及需求变化 带来更多的便利。
【SQLSERVER】递归查询算法实例
一、递归查询
1.结构: 递归CTE
最少包含两个查询(也被称为成员)。
第一个查询为定点成员,定点成员只是一个返回有效表的查询,用于递归的基础或定位点。
第二个查询被称为递归成员,使该查询称为递归成员的是对CTE
名称的递归引用是触发。在逻辑上可以将CTE名称的内部应用理解为前一个查询的结果集。
2.递归结束条件:
第二个递归查询返回空结果集或是超出了递归次数的最大限制时才停止递归。
3.Sql递归的优点:
效率高,大量数据集下,速度比程序的查询快。
4.递归查询的作用:
用来查询指定成员及其递归成员(下属所有成员,包括下属的下属)
5.适用场景:
成员权限查询、等级区域查询,其他相关类似查询。
二、实例
数据准备:
/*
test表
ID 地区ID
Name 地区名称
Main_ID 地区所属上级ID
Sign 地区等级 例如:福建-厦门-湖里 分别是 1,2,3
*/
SELECT 1003 ID, '福建' Name, 0 Main_ID, 1 Sign INTO test union all
SELECT 1050 , '福州' , 1003 , 2 union all
SELECT 1051 , '厦门' , 1003 , 2 union ALL
SELECT 1375 , '思明' , 1051 , 3 union all
SELECT 1382 , '海沧' , 1051 , 3 union all
SELECT 1381 , '湖里' , 1051 , 3 union all
SELECT 1374 , '集美' , 1051 , 3 union all
SELECT 1373 , '同安' , 1051 , 3 union all
SELECT 1380 , '翔安' , 1051 , 3 union ALL
SELECT 667582720122 , '鼓楼' , 1050 , 3 union all
SELECT 667582725528 , '台江' , 1050 , 3 union all
SELECT 667582729587 , '仓山' , 1050 , 3 union all
SELECT 667582732602 , '马尾' , 1050 , 3 union all
SELECT 667582735385 , '晋安' , 1050 , 3 union all
SELECT 667582738507 , '闽侯' , 1050 , 3 union all
SELECT 667582742586 , '连江' , 1050 , 3 union all
SELECT 667582745634 , '罗源' , 1050 , 3 union all
SELECT 667582748358 , '闽清' , 1050 , 3 union all
SELECT 667582751824 , '永泰' , 1050 , 3 union all
SELECT 667582755215 , '平潭' , 1050 , 3 union all
SELECT 667582760309 , '福清' , 1050 , 3 union all
SELECT 667582764565 , '长乐' , 1050 , 3
实例:
/*
查询:福建省(ID 1003)及其底下的所有地区
*/
WITH CTE AS
(
--父项
SELECT ID,Main_ID
FROM test WHERE ID=1003
UNION ALL
--递归结果集中的下级
SELECT a.ID,a.Main_ID
FROM test a
INNER JOIN CTE b ON b.ID=a.Main_ID
)
SELECT a.*
FROM Test a
INNER JOIN CTE t ON a.ID=t.ID
查询结果:
SqlServer 递归查询
--查询部门及下属部门列表
WITH TEMP --递归
AS (SELECT Id,
Code,
Name,
ParentId
FROM [dbo].[AspSysDepartments]
WHERE Id = 38 --查询当前部门
UNION ALL
SELECT B.Id, --查询子部门
B.Code,
B.Name,
B.ParentId
FROM TEMP A
INNER JOIN [dbo].[AspSysDepartments] B
ON B.ParentId = A.Id)
SELECT Id,
Code,
Name,
ParentId
FROM TEMP --获取递归后的集合
结果: