SQLServer 遞迴查詢，SQLServer遞迴

SQLServer 遞迴查詢，SQLServer遞迴

--SQLServer 遞迴查詢，主要用於建立樹形結構IF OBJECT_ID('Categories') IS NOT NULL    DROP TABLE CategoriesGOCREATE TABLE Categories (    CategoryID INT,     CategoryName VARCHAR(20),    ParentID INT )GOINSERT INTO Categories(CategoryID, CategoryName, ParentID)    SELECT 1, 'Books', NULL UNION ALL    SELECT 2, 'SQL Server', 1 UNION ALL    SELECT 3, 'ASP.NET', 1 UNION ALL    SELECT 4, 'General', 2 UNION ALL    SELECT 5, 'SSIS', 2 UNION ALL    SELECT 6, 'TSQL', 2 UNION ALL    SELECT 7, 'SSRS', 2 UNION ALL    SELECT 8, 'Beginners', 4 UNION ALL    SELECT 9, 'Certification Guide', 4goWITH cte AS (    SELECT 0 AS lvl, CategoryID, CategoryName, ParentID,        CAST(CategoryID AS VARCHAR(128)) AS Sort    FROM Categories WHERE ParentID IS NULL    UNION ALL    SELECT p.lvl + 1, c.CategoryID, c.CategoryName, c.ParentID,        CAST(p.Sort + '/' + CAST(c.CategoryID AS VARCHAR) AS VARCHAR(128))    FROM Categories c    INNER JOIN cte p ON p.CategoryID = c.ParentID)SELECT     CategoryID,     SPACE(lvl * 4) + CategoryName AS CategoryName,     Sort,    ParentID FROM cteORDER BY Sort

轉載於http://beyondrelational.com/modules/2/blogs/28/posts/10486/recursive-cte-and-ordering-of-the-hierarchical-result.aspx