TSQL递归

摘要：在程序中调用递归很简单只要在方法中调用自身就可以了，可是在数据库操作中这种方法并不可行，那么下面就来看一下在SQL Server中如何书写递归调用。

主要内容：

可以说直到SQL Server 2005之前，SQL Server在运行时级别是没有对递归处理的设计的。这也就是说如果你想要实现递归就只能自己控制递归逻辑，将递归转换为循环操作。但是到了SQL Server2005之后，递归的书写方式应该说就简单的多了，因为SQL Server 2005引入了CTE（Common Table Expression 公用表表达式）。下面是msdn中关于CTE的简单说明：

可以将公用表表达式 (CTE) 视为临时结果集，在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内进行定义。CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。

CET 可用于：

创建递归查询。有关详细信息，请参阅使用公用表表达式的递归查询。

在不需要常规使用视图时替换视图，也就是说，不必将定义存储在元数据中。

启用按从标量嵌套 select 语句派生的列进行分组，或者按不确定性函数或有外部访问的函数进行分组。

在同一语句中多次引用生成的表。

使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。

递归 CTE 由下列三个元素组成：

例程的调用。

递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集，所以它们被称为"定位点成员"。

CTE_query_definitions 被视为定位点成员，除非它们引用了 CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前，而且必须使用 UNION ALL 运算符联接最后一个定位点成员和第一个递归成员。

例程的递归调用。

递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符联接的 CTE_query_definitions。这些查询定义被称为"递归成员"。

终止检查。

终止检查是隐式的；当上一个调用中未返回行时，递归将停止。

有了CTE之后在SQL Server中使用递归就简单的多了，因为你可以完全不用考虑递归调用的结束条件了，真个递归的调用流程交给了数据库本身来处理。下面直接来看一下递归的使用，首先使用下面的语句来构建一个递归的使用环境：

--Create Table
IF EXISTS (SELECT [name] FROM dbo.sysobjects WHERE [name]='Tree' AND type='u' )
    BEGIN 
        IF EXISTS(SELECT [name] FROM dbo.sysobjects WHERE [name]='Info' AND type='u')
            DROP TABLE Info
        DROP TABLE Tree
    END
ELSE
    BEGIN
        CREATE TABLE Tree 
        (
            id BIGINT PRIMARY KEY,
            [name] NVARCHAR(50) NOT NULL,
            parentID BIGINT FOREIGN KEY REFERENCES Tree(id) ON DELETE NO ACTION NOT NULL
        )
    END
IF EXISTS(SELECT [name] FROM dbo.sysobjects WHERE [name]='Info' AND type='u')
    DROP TABLE Info
ELSE
    BEGIN
        CREATE TABLE Info
        (
            id BIGINT PRIMARY KEY FOREIGN KEY REFERENCES Tree(id) ON DELETE CASCADE,
            info NVARCHAR(500)
        )
    END    
-- Insert Data
DELETE FROM dbo.Tree
DELETE FROM dbo.Info
INSERT INTO dbo.Tree VALUES(1,'A',0)
INSERT INTO dbo.Tree VALUES(2,'B',1)
INSERT INTO dbo.Tree VALUES(3,'C',1)
INSERT INTO dbo.Tree VALUES(4,'D',2)
INSERT INTO dbo.Tree VALUES(5,'E',2)
INSERT INTO dbo.Tree VALUES(6,'F',3)
INSERT INTO dbo.Tree VALUES(7,'G',3)
INSERT INTO dbo.Tree VALUES(8,'H',4)
INSERT INTO dbo.Tree VALUES(9,'I',4)
INSERT INTO dbo.Tree VALUES(10,'J',4)
INSERT INTO info VALUES(1,'AA')
INSERT INTO info VALUES(2,'BB')
INSERT INTO info VALUES(3,'CC')
INSERT INTO info VALUES(4,'DD')
INSERT INTO info VALUES(5,'EE')
INSERT INTO info VALUES(6,'FF')
INSERT INTO info VALUES(7,'GG')
INSERT INTO info VALUES(8,'HH')
INSERT INTO info VALUES(9,'II')
INSERT INTO info VALUES(10,'JJ')    

 

在这个表中存储了有关子节点和父节点的关系，而且你不知道深度有多大，现在的需求是需要找到任意父节点的所有子节点，在使用CTE实现之前还是回顾一下没有CTE之前的写法吧：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        KenshinCui
-- Create date: 2011.12.26
-- Description:    取得所有子节点
-- =============================================
CREATE FUNCTION GetChildren
(    
    @ID INT 
)
RETURNS @tbChildren TABLE(ID INT,Level INT)
AS
    BEGIN
        DECLARE @Level INT
        SET @Level=1 
        INSERT INTO @tbChildren(ID,Level) SELECT id,@Level FROM dbo.Tree WHERE parentID=@ID
        WHILE @@ROWCOUNT<>0
            BEGIN    
                SET @Level=@Level+1
                INSERT INTO @tbChildren(ID,Level) SELECT id,@Level FROM dbo.Tree WHERE parentID IN (SELECT ID FROM @tbChildren WHERE Level=(@Level-1) )
            END
        RETURN
    END
GO

在之前的文章"SQL之树形结构无限级联删除"中事实上也是用的递归来进行解决的，实现原理同上面一样（都是将递归转换为循环，然后通过控制循环结束条件来结束调用）只是上面可能会简洁一些。下面来看使用CTE如何解决这个问题吧：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        KenshinCui
-- Create date: 2011.12.30
-- Description:    取得所有子节点
-- =============================================
CREATE FUNCTION GetChildren
(
    @ID INT
)
RETURNS 
@tbChildren TABLE 
(
    ID INT 
)
AS
BEGIN
    
    WITH ChildrenCTE(ID)
    AS
    (
        SELECT ID FROM dbo.Tree WHERE parentID=@ID
        UNION ALL
        SELECT Tree.ID FROM Tree INNER JOIN ChildrenCTE ON ChildrenCTE.ID = Tree.parentID
    )
    INSERT INTO @tbChildren  SELECT Id FROM ChildrenCTE
    
    RETURN
END
GO

可以看到递归的流程不用手动干预了，结束条件也不用再手动判断，第一次调用SELECT ID FROM dbo.Tree WHERE parentID=@ID将ID放到ChildrenCTE中，第二次调用SELECT Tree.ID FROM Tree INNER JOIN ChildrenCTE ON ChildrenCTE.ID = Tree.parentID（当然此时已经有第一次调用得到的ID）如此反复执行此语句（并且注意每次从ChildrenCTE中取值是会排除上次取过的值，否则递归就用于不可能终止了）直到此语句不再返回行为止。这时所有的数据也都已经放到ChildrenCTE中了，如何操作当然就随你了。

需要注意的是CTE默认递归的层次是100，如果无法满足你的需求可以通过OPTION(MAXRECURSION Level)进行设置,例如上面如果希望只查询2层以内的数据只需要在查询ChildrenCTE时加上OPTION(MAXRECURSION 2)即可。

本作品采用知识共享署名 2.5 中国大陆许可协议进行许可，欢迎转载，演绎或用于商业目的。但转载请注明来自崔江涛（KenshinCui），并包含相关链接。