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