SQL Server 2005中的CTE

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

这种语法的好处就是可以创建出一张临时的表，这张表可以在定义中使用自引用，使得我们处理父-子关系变得前所未有的方便，T-SQL中也有这个功能啦。

还是老规矩，给大家一段比较简单的代码，BOL里面也有，不过是要先理解Adventure Works范例数据库，大家可以去听我的WebCast哦，有这么一讲的，嘿嘿！

--------------------------------------------------------------------------------

USE AdventureWorks

IF EXISTS(SELECT [name] FROM sys.tables WHERE [name] = 'P_C')

DROP TABLE P_C

CREATE TABLE P_C (ID int, ParentID int)

INSERT INTO P_C VALUES (0,0)

INSERT INTO P_C VALUES (1,0)

INSERT INTO P_C VALUES (2,0)

INSERT INTO P_C VALUES (3,1)

INSERT INTO P_C VALUES (4,2)

INSERT INTO P_C VALUES (5,3)

INSERT INTO P_C VALUES (6,0)

INSERT INTO P_C VALUES (7,4)

INSERT INTO P_C VALUES (8,5)

INSERT INTO P_C VALUES (9,8)

INSERT INTO P_C VALUES (10,4)

INSERT INTO P_C VALUES (11,10)

INSERT INTO P_C VALUES (12,2)

INSERT INTO P_C VALUES (13,3)

INSERT INTO P_C VALUES (14,6)

INSERT INTO P_C VALUES (15,14)

INSERT INTO P_C VALUES (16,11)

INSERT INTO P_C VALUES (17,14)

INSERT INTO P_C VALUES (18,15)

INSERT INTO P_C VALUES (19,18)

INSERT INTO P_C VALUES (20,7)

INSERT INTO P_C VALUES (21,9)

INSERT INTO P_C VALUES (22,9)

INSERT INTO P_C VALUES (23,12)

INSERT INTO P_C VALUES (24,13)

INSERT INTO P_C VALUES (25,12)

INSERT INTO P_C VALUES (26,15)

--CTE的实现代码

WITH PCV(P, C) AS

(

SELECT P.ID, P.ParentID

FROM P_C AS P WHERE P.ParentID = 5

UNION ALL

SELECT C.ID, C.ParentID

FROM P_C AS C INNER JOIN PCV ON C.ParentID = PCV.P

)

SELECT * FROM PCV

--利用存储过程实现

CREATE PROCEDURE dbo.usp_GetHierarchy

@ParentID int

WITH PCV(P, C) AS

(

SELECT P.ID, P.ParentID

FROM P_C AS P WHERE P.ParentID = @ParentID

UNION ALL

SELECT C.ID, C.ParentID

FROM P_C AS C INNER JOIN PCV ON C.ParentID = PCV.P

)

SELECT * FROM PCV

EXEC dbo.usp_GetHierarchy 3

EXEC dbo.usp_GetHierarchy 5

EXEC dbo.usp_GetHierarchy 8

EXEC dbo.usp_GetHierarchy 9

--清理数据库

DROP PROCEDURE dbo.usp_GetHierarchy

DROP TABLE P_C