SQL递归菜单排序 [英] SQL Recursive Menu Sorting

问题描述

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

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]

这篇关于SQL递归菜单排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！