--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
--SQLServer 递归查询,主要用于建立树形结构IF OBJECT_ID('Categories') IS NOT NULL DROP TABLE CategoriesGOCREATE TABLE Categories ( CategoryID INT, CategoryName VARCHAR(20), ParentID INT )GOIN