Sql Server中的递归查询及 CTE(公用表达式)

公用表表达式
在Microsoft SQL Server 2008系统中，可以使用公用表表达式(common table expression，CTE)。CTE是定义在SELECT、INSERT、UPDATE或DELETE语句中的临时命名的结果集，CTE也可以用在视图的定义中。在CTE中可以包括对自身的引用，因此这种表达式也被称为递归CTE。

在SELECT语句中，可以使用WITH子句定义CTE。CTE的基本语法形式如下：

–WITH expression_name (column_name, …)

–AS

–(CTE_query_definition)

我们用几个案例来说话，如图：

从上一张图总结：WITH后跟表名(列名,列名,列名) AS后面的括号中接表达式。最后接SELECT 所有的WITH列+FROM表名

 

在实际查询中，时常会碰到需要递归查询的例子，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形式。