sql - 分层 CTE，每级附加排序列

Question

CTE 对我来说有点新，所以我希望有人可以帮助我编写的以下内容将采用类别表并从中构建层次结构以进行显示。我知道这种事情一直被问到，但我认为我的排序情况使它有点独特。

我希望有一些使用 HierarchyID 的建议，但遗憾的是，由于一长串与此处无关的原因，这不是一个选项。我提出的解决方案虽然有效并为我提供了我期望的数据，但我想知道是否有更好/更优雅的方法来实现这一点。

基本要求如下:

类别可以有无限数量的子项

类别可以是无限数量的深度

具有相同父级的类别将根据“排序”字段进行排序。如果未指定(默认为 0)或与另一个同级类别相同，它将按字母顺序排序。

表定义:

CREATE TABLE [dbo].[TreeTest]
(
    [id] [int] NOT NULL,
    [parent] [int] NULL,
    [title] [varchar](50) NOT NULL,
    [sort] [int] NOT NULL
)
GO

ALTER TABLE [dbo].[TreeTest] ADD  CONSTRAINT [DF_TreeTest_sort]  DEFAULT ((0)) FOR [sort]
GO

插入语句:

INSERT TreeTest(id,parent,title,sort) VALUES('1',NULL,'Parent 1','0')
INSERT TreeTest(id,parent,title,sort) VALUES('2',NULL,'Parent 2','0')
INSERT TreeTest(id,parent,title,sort) VALUES('3',NULL,'Parent 3','2')
INSERT TreeTest(id,parent,title,sort) VALUES('4',NULL,'Parent 4','1')
INSERT TreeTest(id,parent,title,sort) VALUES('5','1','Child 1a','0')
INSERT TreeTest(id,parent,title,sort) VALUES('6','2','Child 2a','0')
INSERT TreeTest(id,parent,title,sort) VALUES('7','3','Child 3a','0')
INSERT TreeTest(id,parent,title,sort) VALUES('8','1','Child 1b','1')
INSERT TreeTest(id,parent,title,sort) VALUES('9','1','Child 1c','2')
INSERT TreeTest(id,parent,title,sort) VALUES('10','1','Child 1d','1')
INSERT TreeTest(id,parent,title,sort) VALUES('11','6','Child 2a 1','0')
INSERT TreeTest(id,parent,title,sort) VALUES('12','6','Child 2a 2','1')
INSERT TreeTest(id,parent,title,sort) VALUES('13','6','Child 2a 3','0')
INSERT TreeTest(id,parent,title,sort) VALUES('14','6','Child 2a 4','2')

CTE:

WITH TreeList (id, parent, title, sort, title_path, level_id, level_id_path) as
(
    SELECT p.id, 
           p.parent, 
           p.title, 
           p.sort,
           CONVERT(nvarchar(max), p.title), 
           ROW_NUMBER() OVER(PARTITION BY parent ORDER BY p.sort, p.title), 
           CAST(ROW_NUMBER() OVER(PARTITION BY parent ORDER BY p.sort) AS varchar(max))
        FROM TreeTest p
        WHERE p.parent is null
    UNION ALL
    SELECT c.id, 
           c.parent, 
           c.title, 
           c.sort,
           r.title_path + '/' + c.title, 
           ROW_NUMBER() OVER(PARTITION BY c.parent ORDER BY c.sort, c.title), 
           CONVERT(varchar(max), r.level_id_path + '.' + CAST(ROW_NUMBER() OVER(PARTITION BY c.parent ORDER BY c.sort, c.title) AS VARCHAR))
        FROM TreeTest AS c
        INNER JOIN treelist AS r
            ON c.parent = r.id
)
SELECT *
FROM TreeList
ORDER BY level_id_path

输出(我认为图像是显示输出的最简单方法)



同样，这根据我的规范工作，但我不确定效率以及是否有更好的方法来做到这一点。当我查看执行计划时，似乎最昂贵的部分是排序/索引扫描，但鉴于此示例中缺少索引，这似乎是意料之中的。如果有人有任何意见，将不胜感激。

Answer 1

order by case when parent=0 then parentid else id end

这里 id 表示表上的主键