在sql中通过DFS搜索对自引用表进行排序

Question

我有一个自我引用的表，它表示一些数据，如下所示：

    declare @table table( 
    ID int,
    ParentID int,
    Name varchar(50),
    levelNode int
)
declare @Temptable table( 
    ID int,
    ParentID int,
    Name varchar(50),
    levelNode int
)
declare @maxlevel int

insert into @table (ID,ParentID,Name,levelNode) select 11,null,'A',1
insert into @table (ID,ParentID,Name,levelNode) select 12,11,'B-1',2
insert into @table (ID,ParentID,Name,levelNode) select 13,11,'B-2',2
insert into @table (ID,ParentID,Name,levelNode) select 14,12,'B-1-1',3
insert into @table (ID,ParentID,Name,levelNode) select 15,12,'B-1-2',3
insert into @table (ID,ParentID,Name,levelNode) select 16,12,'B-1-3',3
insert into @table (ID,ParentID,Name,levelNode) select 17,13,'B-2-1',3
insert into @table (ID,ParentID,Name,levelNode) select 18,13,'B-2-2',3
insert into @table (ID,ParentID,Name,levelNode) select 19,13,'B-2-3',3
insert into @table (ID,ParentID,Name,levelNode) select 20,19,'B-2-3-1',4
insert into @table (ID,ParentID,Name,levelNode) select 21,19,'B-2-3-2',4
insert into @table (ID,ParentID,Name,levelNode) select 22,17,'B-2-1-1',4
insert into @table (ID,ParentID,Name,levelNode) select 23,17,'B-2-1-2',4

declare @ID int
select @ID=11;
With ret AS(
    select * from @table
    where ID=@ID
    union all
    select t.* from @table t inner join ret r ON t.ParentID=r.ID 
)

insert into @Temptable select * from ret where ID<>@ID
order by ??????????????????

select * from @Temptable

​

​

我想这样排序：

​

​

我应该按章节顺序写些什么呢！

Answer 1

试试这个..。

Fiddle demo

INSERT INTO @Temptable 
SELECT * 
FROM   ret 
WHERE  id <> @ID 
ORDER  BY Substring(NAME, 3, 1), 
          Substring(NAME, 5, 1), 
          Substring(NAME, 7, 1)