玫瑰虽美，当心其刺；CTE虽好，注意其规则

CTE 确实很好很强大，尤其在处理递归调用方面，非常简单实用。

下面的例子就讲述了通过 CTE 递归调用获取 ParentAndChildren 表中 Folder2 的子孙节点，不过 CTE 的使用有很多规则需要注意，忽略了这些规则，它的刺可能会刺伤我们。

详细规则请参照：

http://msdn.microsoft.com/zh-cn/library/ms190766.aspx

 

这里主要讲述大家最容易忽略的一条规则：

 

•CTE 之后必须跟随引用部分或全部 CTE 列的一条 SELECT 、 INSERT 、 UPDATE 、 MERGE 或 DELETE -- 语句。也可以在 CREATE VIEW 语句中将 CTE 指定为视图中 SELECT 定义语句的一部分。

 

示例语句：

 

 

CREATE DATABASE testdb20100730

GO

USE testdb20100730;

 

CREATE TABLE ParentAndChildren

(

     Id     INT ,

     Name    NVARCHAR ( 50),

     Parent INT ,

     CONSTRAINT pk_ParentAndChildren PRIMARY KEY CLUSTERED

        (

            [Id] ASC

        ) WITH ( IGNORE_DUP_KEY = OFF ) ON [PRIMARY]

)

ON [PRIMARY]

 

INSERT INTO ParentAndChildren( Id, Name , Parent)

SELECT 0, 'root' ,null

UNION ALL

SELECT 1, 'folder1' , 0

UNION ALL

SELECT 2, 'folder2' , 0

UNION ALL

SELECT 3, 'folder3' , 0

UNION ALL

SELECT 4, 'file1' , 1

UNION ALL

SELECT 5, 'file2' , 2

UNION ALL

SELECT 6, 'folrder4' , 2

UNION ALL

SELECT 7, 'file3' , 6

UNION ALL

SELECT 8, 'folrder5' , 6

UNION ALL

SELECT 9, 'file3' , 8

 

GO

 

DECLARE @I INT ;

 

WITH cteParentChildren( Id, Name , Parent, [Level]) AS

(

    SELECT Id, Name , Parent, 0 AS [Level]

    FROM dbo. ParentAndChildren

    WHERE Parent = 0

            AND Name LIKE 'Folder2'

    UNION ALL

    SELECT e. Id, e. Name , e. Parent, Level + 1

    FROM dbo. ParentAndChildren AS e

        INNER JOIN cteParentChildren AS d ON e. Parent = d. Id

)

--DECLARE @I INT; 如果在此处声明变量 @I 就违反上述规则会报错

---SELECT @I = 1 如果此处执行 SELECT 赋值语句仍会报错，不过错误 是“ 声明了 CTE“cteParentChildren” 却没有使用 ！”，此时下面的语句将不能执行

 

SELECT * FROM cteParentChildren

 

USE master;

 

DROP DATABASE testdb20100730