时间:2021-07-01 10:21:17 帮助过:38人阅读
--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
转载于http://beyondrelational.com/modules/2/blogs/28/posts/10486/recursive-cte-and-ordering-of-the-hierarchical-result.aspx
SQLServer 递归查询
标签: