SQLServer 递归查询

最新推荐文章于 2024-09-13 16:10:59 发布

原创最新推荐文章于 2024-09-13 16:10:59 发布 · 2.3k 阅读

0 ·

CC 4.0 BY-SA版权

技术文章专栏收录该内容

19 篇文章

订阅专栏

本文介绍如何使用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, 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 cte
ORDER BY Sort