SQL SERVER 递归查询（1）——常用方法（CTE写法、函数）

      我们在实际查询中，时常会碰到需要递归查询的例子，SQL SERVER 2005之前的版本可以用函数方法实现，SQL SERVER 2005之后可以利用CTE（公用表表达式Common Table Expression是SQL SERVER 2005版本之后引入的一个特性）的方式来查询。

--测试数据
if not object_id(N'T') is null
	drop table T
Go
Create table T([id] int,[pid] int,[num] int)
Insert T
select 1,0,1 union all
select 2,1,1 union all
select 3,2,1 union all
select 4,2,1 union all
select 5,2,1 union all
select 6,3,1 union all
select 7,3,1
Go
--测试数据结束 

      我们想要向上累积的结果，计算每级的子集集合是多少，函数方式，新建函数：

IF OBJECT_ID('dbo.f_GetChildren') IS NOT NULL
    DROP FUNCTION dbo.f_GetChildren
GO
CREATE FUNCTION f_GetChildren
    (
      @id INT ,
      @pid INT ,
      @num INT
    )
RETURNS @tab TABLE
    (
      [id] INT ,
      [pid] INT ,
      [num] INT
    )
AS
    BEGIN
        INSERT  @tab
                SELECT  @id ,
                        @pid ,
                        @num
        WHILE @@rowcount > 0
            BEGIN               
                INSERT  @tab
                        SELECT  T.id ,
                                T.pid ,
                                T.num
                        FROM    T
                                JOIN @tab t1 ON T.pid = t1.id
                        WHERE   NOT EXISTS ( SELECT *
                                             FROM   @tab
                                             WHERE  T.id = [@tab].id )
            END
        RETURN  
    END
	GO

      调用：

SELECT  id ,
        ( SELECT    SUM(num)
          FROM      f_GetChildren(id, pid, num)
        ) AS sumnum
FROM    T

      结果：

      利用CTE的方式:

;WITH cte AS (
SELECT *,id AS sumid FROM dbo.T
UNION ALL
SELECT T.*,cte.sumid FROM T JOIN cte ON T.pid=cte.ID
)
SELECT sumid,SUM(num) AS sumnum FROM cte GROUP BY sumid

      结果：

      以上是递归的两种基本实现方式，函数和CTE形式。