SQL Server递归查询

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   --获取递归后的集合

结果：