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)
GO
--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
GO
--利用存储过程实现
CREATE PROCEDURE dbo.usp_GetHierarchy
@ParentID int
AS
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
GO
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
