SQLServer 递归查询

--SQLServer 递归查询，主要用于建立树形结构

IF OBJECT_ID('Categories') IS NOT NULL
    DROP TABLE Categories
GO

CREATE TABLE Categories (
    CategoryID INT,CategoryName VARCHAR(20),ParentID INT )
GO

INSERT 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',4
go

WITH cte AS (
    SELECT 0 AS lvl,CategoryID,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 cte
ORDER BY Sort

转载于http://beyondrelational.com/modules/2/blogs/28/posts/10486/recursive-cte-and-ordering-of-the-hierarchical-result.aspx

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!