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