[转载]SQL Server 2005中的CTE
<p>信息来源: CSDN BLOG</p><p><span id="ArticleContent1_ArticleContent1_lblContent"></span><br /></p><p><span class="Title">SQL Server 2005中的CTE</span><br />SQL Server 2005中有一种新的语法叫做通用表表达式，CTE（Common Table Expression）。</p><p>这种语法的好处就是可以创建出一张临时的表，这张表可以在定义中使用自引用，使得我们处理父-子关系变得前所未有的方便，T-SQL中也有这个功能啦。</p><p>还是老规矩，给大家一段比较简单的代码，BOL里面也有，不过是要先理解Adventure Works范例数据库，大家可以去听我的WebCast哦，有这么一讲的，嘿嘿！</p><p></p><p>--------------------------------------------------------------------------------</p><p><br />USE AdventureWorks<br />IF EXISTS(SELECT [name] FROM sys.tables WHERE [name] = 'P_C')<br />DROP TABLE P_C<br />CREATE TABLE P_C (ID int, ParentID int)</p><p>INSERT INTO P_C VALUES (0,0)<br />INSERT INTO P_C VALUES (1,0)<br />INSERT INTO P_C VALUES (2,0)<br />INSERT INTO P_C VALUES (3,1)<br />INSERT INTO P_C VALUES (4,2)<br />INSERT INTO P_C VALUES (5,3)<br />INSERT INTO P_C VALUES (6,0)<br />INSERT INTO P_C VALUES (7,4)<br />INSERT INTO P_C VALUES (8,5)<br />INSERT INTO P_C VALUES (9,8)<br />INSERT INTO P_C VALUES (10,4)<br />INSERT INTO P_C VALUES (11,10)<br />INSERT INTO P_C VALUES (12,2)<br />INSERT INTO P_C VALUES (13,3)<br />INSERT INTO P_C VALUES (14,6)<br />INSERT INTO P_C VALUES (15,14)<br />INSERT INTO P_C VALUES (16,11)<br />INSERT INTO P_C VALUES (17,14)<br />INSERT INTO P_C VALUES (18,15)<br />INSERT INTO P_C VALUES (19,18)<br />INSERT INTO P_C VALUES (20,7)<br />INSERT INTO P_C VALUES (21,9)<br />INSERT INTO P_C VALUES (22,9)<br />INSERT INTO P_C VALUES (23,12)<br />INSERT INTO P_C VALUES (24,13)<br />INSERT INTO P_C VALUES (25,12)<br />INSERT INTO P_C VALUES (26,15)<br />GO</p><p>--CTE的实现代码<br />WITH PCV(P, C) AS<br />(<br /> SELECT P.ID, P.ParentID<br /> FROM P_C AS P WHERE P.ParentID = 5<br /> UNION ALL<br /> SELECT C.ID, C.ParentID<br /> FROM P_C AS C INNER JOIN PCV ON C.ParentID = PCV.P<br />)<br />SELECT * FROM PCV<br />GO</p><p>--利用存储过程实现<br />CREATE PROCEDURE dbo.usp_GetHierarchy<br />@ParentID int<br />AS<br />WITH PCV(P, C) AS<br />(<br /> SELECT P.ID, P.ParentID<br /> FROM P_C AS P WHERE P.ParentID = @ParentID<br /> UNION ALL<br /> SELECT C.ID, C.ParentID<br /> FROM P_C AS C INNER JOIN PCV ON C.ParentID = PCV.P<br />)<br />SELECT * FROM PCV<br />GO</p><p>EXEC dbo.usp_GetHierarchy 3<br />EXEC dbo.usp_GetHierarchy 5<br />EXEC dbo.usp_GetHierarchy 8<br />EXEC dbo.usp_GetHierarchy 9</p><p>--清理数据库<br />DROP PROCEDURE dbo.usp_GetHierarchy<br />DROP TABLE P_C</p><p></p>页:
[1]