SQL递归菜单排序

本文介绍了SQL递归菜单排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个简单的表格，我用它来表示类别的层次结构.

I've got a simple table that I'm using to represent a hierarchy of categories.

CREATE TABLE [dbo].[Categories]( [ID] [int] IDENTITY(1,1) NOT NULL, [Title] [varchar](256) NOT NULL, [ParentID] [int] NOT NULL, CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('All', 0) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Banking', 8) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('USAA Checking', 2) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('USAA Mastercard', 2) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Medical', 8) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Jobs', 8) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Archive', 1) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Active', 1) INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('BoA Amex', 2)

除了选择整棵树之外，一切都很好.这是我的查询，我删除了 ORDER BY，因为它不起作用:

Everything is fine except for selecting the entire tree. Here is my query, I removed my ORDER BY because it doesn't work:

WITH CategoryTree (ID, Title, Level, ParentID) AS ( SELECT r.ID, r.Title, 0 Level, r.ParentID FROM Categories r WHERE r.ParentID = 0 UNION ALL SELECT c.ID, c.Title, p.Level + 1 AS Level, c.ParentID FROM Categories c INNER JOIN CategoryTree p ON p.ID = c.ParentID ) SELECT ID, REPLICATE('-----', Level) + Title AS Title, ParentID FROM CategoryTree

结果:

ID Title ParentID 1 All 0 7 -----Archive 1 8 -----Active 1 2 ----------Banking 8 5 ----------Medical 8 6 ----------Jobs 8 3 ---------------USAA Checking 2 4 ---------------USAA Mastercard 2 9 ---------------BoA Amex 2

我想要的结果是:

ID Title ParentID 1 All 0 8 -----Active 1 2 ----------Banking 8 9 ---------------BoA Amex 2 3 ---------------USAA Checking 2 4 ---------------USAA Mastercard 2 6 ----------Jobs 8 5 ----------Medical 8 7 -----Archive 1

让我丧命的是我之前可以完美运行，但后来我忘记备份数据库并在服务器升级中丢失了它.

What is killing me is I got this working perfectly before but then I forgot to back up the DB and lost it in a server upgrade.

我在 2008 年查看了 HierarchyID 类型，但如果您关心同一级别的孩子的顺序，这似乎是一件很痛苦的事情.

I looked at the HierarchyID type in 2008 but it just seems like a big pain in the ass if you care about order of children at the same level.

推荐答案

好的，明白了 :) -- 这在这里似乎有效.

Ok, got it :) -- This seems to work here.

DECLARE @Categories TABLE ( ID int PRIMARY KEY ,Title varchar(256) ,ParentID int ) INSERT INTO @Categories VALUES (1, 'All', 0) ,(2,'Banking', 8) ,(3,'USAA Checking', 2) ,(4,'USAA Mastercard', 2) ,(5,'Medical', 8) ,(6,'Jobs', 8) ,(7,'Archive', 1) ,(8,'Active', 1) ,(9,'BoA Amex', 2) ; WITH CategoryTree AS (SELECT r.ID, r.Title, 0 Level, r.ParentID, CAST(r.Title AS VARCHAR(1000)) AS "Path" FROM @Categories r WHERE r.ParentID = 0 UNION ALL SELECT c.ID, c.Title, p.Level + 1 AS Level, c.ParentID, CAST((p.path + '/' + c.Title) AS VARCHAR(1000)) AS "Path" FROM @Categories c INNER JOIN CategoryTree p ON p.ID = c.ParentID ) SELECT ID, REPLICATE('-----', Level) + Title AS Title, [Path] FROM CategoryTree ORDER BY [Path]