CTE

[ WITH <common_table_expression> [ ,n ] ] 
<common_table_expression>::= 
        expression_name [ ( column_name [ ,n ] ) ] 
    AS 
        ( CTE_query_definition )

3.CTE的寫法

with 
cr as 
( 
    select CountryRegionCode from person.CountryRegion where Name like 'C%' 
)

select * from person.StateProvince where CountryRegionCode in (select * from cr)

cr 是一個公用表達式，使用上與表變量相似，只是SQL Server 2005處理公用表達式的方式有所不同。

注意事項

1.需要使用CTE的語句要緊跟著CTE，兩者之間不能有其他東西。否則CTE將會失效。

with
cr as
(
    select CountryRegionCode from person.CountryRegion where Name like 'C%'
)
select * from person.CountryRegion  -- 應將這條SQL語句去掉
-- 使用CTE的SQL語句應緊跟在相關的CTE後面 --
select * from person.StateProvince where CountryRegionCode in (select * from cr)

2.多個CTE可以串起來用，但是只能有一個With。

with
cte1 as
(
    select * from table1 where name like 'abc%'
),
cte2 as
(
    select * from table2 where id > 20
),
cte3 as
(
    select * from table3 where price < 100
)
select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id

3.假如CTE的表達式名稱和其他已經存在的table、view同名，緊跟在CTE後面的語句使用的仍是CTE，而再更後面的就是已存在的table、view了。

--  table1是一個實際存在的表

with
table1 as
(
    select * from persons where age < 30
)
select * from table1  --  使用了名為table1的公共表表達式
select * from table1  --  使用了名為table1的數據表

4.CTE可以引用自身，也可以在同一個With子句中預先定義的CTE，不允許前向引用。

--使用遞歸公用表表達式顯示遞歸的多個級別
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports ;

--使用遞歸公用表表達式顯示遞歸的兩個級別
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports 
WHERE EmployeeLevel <= 2 

--使用遞歸公用表表達式顯示層次列表
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort

--使用 MAXRECURSION 取消一條語句
--可以使用 MAXRECURSION 來防止不合理的遞歸 CTE 進入無限循環。以下示例特意創建了一個無限循環，然後使用 MAXRECURSION 提示將遞歸級別限制為兩級
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2)
--在更正代碼錯誤之後，就不再需要 MAXRECURSION。以下示例顯示了更正後的代碼
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte

5.不能在 CTE_query_definition 中使用以下子句：

（1）COMPUTE 或 COMPUTE BY

（2）ORDER BY（除非指定了 TOP 子句）

（3）INTO

（4）帶有查詢提示的 OPTION 子句

（5）FOR XML

（6）FOR BROWSE

6.如果將 CTE 用在屬於批處理的一部分的語句中，那麼在它之前的語句必須以分號結尾，如下面的SQL所示：

declare @s nvarchar(3)
set @s = 'C%'
;  -- 必須加分號
with
t_tree as
(
    select CountryRegionCode from person.CountryRegion where Name like @s
)
select * from person.StateProvince where CountryRegionCode in (select * from t_tree)

Brian's範例:

;with test as 
  (
    SELECT CAST(Name AS NVARCHAR(255)) AS Path
    ,*
    from TestNode as a
    where ParentNodeID = 0 

    UNION ALL

     SELECT CAST(c.Path + ' > ' + CAST(b.Name AS NVARCHAR(255)) AS NVARCHAR(255))
    ,b.*
    from TestNode as b
    inner join test as c on c.NodeID = b.ParentNodeID
  )
  select Path,NodeID,ParentNodeID
  from test
  order by NodeID