SQL SERVER 2005 中的CTE

SQL Server 2005中有一种新的语法叫做通用表表达式，CTE（Common Table Expression）。

这种语法的好处就是可以创建出一张临时的表，这张表可以在定义中使用自引用，使得我们处理父-子关系变得前所未有的方便.下面举例子
说明之.
USE AdventureWorks
GO

WITH MyCTE( ListPrice, SellPrice) AS
(
  SELECT ListPrice, ListPrice * .95
  FROM Production.Product
)

SELECT *
FROM MyCTE
这里就是罗列两列,将listprice*0.95.
当然,要用临时表的话也可以实现,如下
CREATE TABLE #MyCTE
(
  ListPrice money,
  SellPrice money
)

INSERT INTO #MyCTE
(ListPrice, SellPrice)
SELECT ListPrice, ListPrice * .95
FROM Production.Product  

但要注意的是,在声明CTE后,要立即调用,比如

USE AdventureWorks
GO
WITH MyCTE( ListPrice, SellPrice) AS
(
SELECT ListPrice, ListPrice * .95
FROM Production.Product
)
SELECT *
FROM Production.Location
SELECT *
FROM MyCTE
GO

将出错

    CTE最大的用处就是用在递归查询中了,CTE 引用它本身时，它被视为递归的。递归的 CTE 是根据至少两个查询（或者，用递归查询的说法，为成员）构建的。一个是非递归查询，也称为锚定成员 (AM)。另一个是递归查询，也称为递归成员 (RM)。查询由 UNION ALL 运算符分隔。以下示例显示了递归 CTE 的简化的一般形式：

WITH RecursiveCTE()
AS
(
  -- Anchor Member:
  -- SELECT query that does not refer to RecursiveCTE
  SELECT ...
  FROM
  ...
  UNION ALL
  -- Recursive Member
  -- SELECT query that refers to RecursiveCTE
  SELECT ...
  FROM
    JOIN RecursiveCTE

  ...
)
-- Outer Query
SELECT ...
FROM RecursiveCTE
...

在逻辑上，您可以将实现递归 CTE 的算法视为：

1.
 锚定成员被激活。集 R0（R 表示“结果”）被生成。
 
2.
 递归成员被激活，在引用 RecursiveCTE 时获得集 Ri（i = 步骤号）作为输入。集 Ri + 1 被生成。
 
3.
 步骤 2 的逻辑被反复运行（在每个迭代中递增步骤号），直到返回空集。
 
4.
 外部查询执行，在引用 RecursiveCTE 时，获得以前所有步骤的累积 (UNION ALL) 结果。 
 
举例子说明:

USE tempdb -- or your own test database
CREATE TABLE Employees
(
  empid   int         NOT NULL,
  mgrid   int         NULL,
  empname varchar(25) NOT NULL,
  salary  money       NOT NULL,
  CONSTRAINT PK_Employees PRIMARY KEY(empid),
  CONSTRAINT FK_Employees_mgrid_empid
    FOREIGN KEY(mgrid)
    REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)
INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)
INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)
INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)
INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00)
INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00)
INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00)
INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00)
INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)
INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)
INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)
INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)

每个雇员都向其 ID 存储在 mgrid 列中的经理汇报。在引用 empid 列的 mgrid 列上定义了一个外键，这意味着经理 ID 必须对应于该表中的一个有效雇员 ID 或者为 NULL。老板 Nancy 在 mgrid 列中具有 NULL 值。
  比如要显示有关 Robert (empid=7) 及其所有级别下属的详细信息。

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(

  -- Anchor Member (AM)
  SELECT empid, empname, mgrid, 0
  FROM Employees
  WHERE empid = 7
  UNION ALL
 
  -- Recursive Member (RM)
  SELECT E.empid, E.empname, E.mgrid, M.lvl+1
  FROM Employees AS E
    JOIN EmpCTE AS M
      ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE

empid       empname                   mgrid       lvl       
----------- ------------------------- ----------- -----------
7           Robert                    3           0         
11          David                     7           1         
12          Ron                       7           1         
13          Dan                       7           1         
14          James                     11          2    

lvl是递归的层数,而且是可以控制的,比如

WITH EmpCTEJanet(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 3
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTEJanet as M
ON E.mgrid = M.empid
WHERE lvl < 2
)
SELECT empid, empname
FROM EmpCTEJanet
WHERE lvl = 2

以下为结果集：

empid       empname
----------- -------------------------
11          David
12          Ron
13          Dan