从您的措辞和意外输出来看,似乎您正在寻找一个独特级别和功能的列表。希望以下内容有所帮助。如果不是,请提供更多上下文以了解您正在寻找什么。
declare @table table (Feature_ID int, Parent_ID int);
insert @table values
(24,null),
(24,25),
(20,null),
(26,12),
(12,null),
(21,23),
(26,20),
(22,null),
(24,23),
(23,26),
(24,27),
(27,28),
(24,22),
(29,20),
(23,null),
(25,null),
(27,29),
(22,26),
(28,12);
select * from @table order by 1,2;
select * from @table order by 2,1;
with cte as (
select Feature_ID, Parent_ID, 0 [Level], CAST(Feature_ID as varchar(200)) [Path]
from @table
where Parent_ID is null
union all
select t.Feature_ID, t.Parent_ID, c.[Level] + 1, cast(c.[Path] + '|' + CAST(t.Feature_ID as varchar(200)) as varchar(200))
from @table t
join cte c
on c.Feature_ID = t.Parent_ID
)
select distinct [Level], Feature_ID
from cte
order by [Level], Feature_ID;
Level Feature_ID
0 12
0 20
0 22
0 23
0 24
0 25
1 21
1 24
1 26
1 28
1 29
2 22
2 23
2 27
3 21
3 24
=level()
函数来获取层级字段。 - JohnHC