SQL Server递归查询

偏执的太偏执、 2021-06-26 16:06 576阅读 0赞

1、既然要谈到sql，数据库表是必须的  
![在这里插入图片描述][20190731223857103.png]

2、数据结构  
![在这里插入图片描述][20190731223904357.png]  
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

结果  
![在这里插入图片描述][20190731224043623.png]

--------------------

4、使用节点路径来做（每个节点路径都保存自身的路径和所有父节点的路径=自己和所有父节点的关联）  
![在这里插入图片描述][20190731224106114.png]  
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

查询结果：  
![在这里插入图片描述][20190731224834352.png]

--------------------

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

结果：  
![在这里插入图片描述][20190731225005898.png]

[20190731223857103.png]: /images/20210626/9e66ea7e95834dda892c8ae7f1a31773.png
[20190731223904357.png]: /images/20210626/be216c57f0d34da5a78a586b52b2df3f.png
[20190731224043623.png]: /images/20210626/5afac1e428a04502ab2d48ebffe6e5bc.png
[20190731224106114.png]: /images/20210626/dd8f4880391445f78a24ca55eae69b42.png
[20190731224834352.png]: /images/20210626/d10466ce88fe4f1f84cb0e7e2850e364.png
[20190731225005898.png]: /images/20210626/1f608bdbd42343e3bdd2da3c5f1f652c.png