SQL中的递归用法

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

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

结果如下：

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

下面我们通过层次结构查询子节点到父节点的 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 的长度设置为最大，防止字段过长超出字段长度。具体结果如下：

以上就是递归查询的一些知识介绍了，自己可以动手实验一下，这个一般在面试中也经常会考察面试者，希望能帮助到大家~

阿里云 2 核 2G 服务器 3M 带宽 61 元 1 年，有高配

腾讯云新客低至 82 元 / 年，老客户 99 元 / 年

代金券：在阿里云专用满减优惠券