SQL Server 2005中的CTE

xiahe999

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

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

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

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