SQL中的递归原理

117次阅读

共计 2042 个字符，预计需要花费 6 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章主要介绍“SQL 中的递归原理”，在日常操作中，相信很多人在 SQL 中的递归原理问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”SQL 中的递归原理”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

递归查询原理

SQL Server 中的递归查询是通过 CTE(表表达式) 来实现。至少包含两个查询，第一个查询为定点成员，定点成员只是一个返回有效表的查询，用于递归的基础或定位点; 第二个查询被称为递归成员，使该查询称为递归成员的是对 CTE 名称的递归引用是触发。在逻辑上可以将 CTE 名称的内部应用理解为前一个查询的结果集。

递归查询的终止条件

递归查询没有显式的递归终止条件，只有当第二个递归查询返回空结果集或是超出了递归次数的最大限制时才停止递归。是指递归次数上限的方法是使用 MAXRECURION。

递归查询的优点

效率高，大量数据集下，速度比程序的查询快。

递归的常见形式

WITH CTE AS ( SELECT column1,column2... FROM tablename WHERE conditions UNION ALL SELECT column1,column2... FROM tablename INNER JOIN CTE ON conditions )

递归查询示例

创建测试数据，有一个员工表 Employee，ManagerID 是 UserID 的父节点，这是一个非常简单的层次结构模型。

USE SQL_Road GO CREATE TABLE Employee ( UserID INT, ManagerID INT, Name NVARCHAR(10) ) INSERT INTO dbo.Employee SELECT 1,-1,N Boss  UNION ALL SELECT 11,1,N A1  UNION ALL SELECT 12,1,N A2  UNION ALL SELECT 13,1,N A3  UNION ALL SELECT 111,11,N B1  UNION ALL SELECT 112,11,N B2  UNION ALL SELECT 121,12,N C1

查询一下 Employee 表里的数据

查询每个 User 的的直接上级 Manager

WITH CTE AS( SELECT UserID,ManagerID,Name,Name AS ManagerName FROM dbo.Employee WHERE ManagerID=-1 UNION ALL SELECT c.UserID,c.ManagerID,c.Name,p.Name AS ManagerName FROM CTE P INNER JOIN dbo.Employee c ON p.UserID=c.ManagerID ) SELECT UserID,ManagerID,Name,ManagerName FROM CTE

结果如下：

我们来解读一下上面的代码

1、查询 ManagerID=-1，作为根节点，这是递归查询的起始点。

2、迭代公式是 UNION ALL 下面的查询语句。在查询语句中调用中 CTE，而查询语句就是 CTE 的组成部分，即 “自己调用自己”，这就是递归的真谛所在。

所谓迭代，是指每一次递归都要调用上一次查询的结果集，UNION ALL 是指每次都把结果集并在一起。

3、迭代公式利用上一次查询返回的结果集执行特定的查询，直到 CTE 返回 NULL 或达到最大的迭代次数，默认值是 32。最终的结果集是迭代公式返回的各个结果集的并集，求并集是由 UNION ALL 子句定义的，并且只能使用 UNION ALL

查询路径

下面我们通过层次结构查询子节点到父节点的 PATH，我们对上面的代码稍作修改：

WITH CTE AS( SELECT UserID,ManagerID,Name,CAST(Name AS NVARCHAR(MAX)) AS LPath FROM dbo.Employee WHERE ManagerID=-1 UNION ALL SELECT c.UserID,c.ManagerID,c.Name,p.LPath+ - +c.Name AS LPath FROM CTE P INNER JOIN dbo.Employee c ON p.UserID=c.ManagerID ) SELECT UserID,ManagerID,Name,LPath FROM CTE

其中 CAST(Name AS NVARCHAR(MAX)) 是将 Name 的长度设置为最大，防止字段过长超出字段长度。具体结果如下：