从SQL树中获取根和顶层

Question

我有一个树表。我将得到这棵树的根和顶层

帮助解决方案你可以使用任何你想要的

declare @disc table (
id int,
parent int,
label varchar(50)
)
insert into @disc
select *
from (
values (1, null,    'q_1'),
(2, 1,       'a_1_1'),
(3, 2,       'a_1_1_1'),
(4, 1,       'a_1_2'),
(5, null,    'q_5'),
(6, 5,       'a_5_1'),
(7, 5,       'a_5_2')
) x (id, parent, label);
1. q_1
2. a_1_1
3. a_1_1_1
4. a_1_2
5. q_5
6. a_5_1
7. a_5_2

，我的结果应该是这样的:

1: 1, null, q_1
2: 2, 1,    a_1_1
3: 5, null, q_5
4: 6, 5,    a_5_1

或

1: 1, null, q_1
2: 5, null, q_5
3: 2, 1,    a_1_1
4: 6, 5,    a_5_1

我只找到了一种方法，但我相信有更好的解决方案:

with rec as (
select id, parent, label,
row_number() over(order by id) rnk,
1 lvl
from @disc
where parent is null
union all
select d.id, d.parent, d.label,
row_number() over(order by d.id) rnk,
r.lvl + 1
from rec r
join @disc d on r.id = d.parent
)
select *
from rec
where parent is null or (rnk = 1 and lvl = 2) 

Answer 1

如果我理解这一点，父值将在根节点中为空。下一层将有一个根节点作为父节点。所以…

;with roots as
(
select id, parent, label
from @disc
where parent is null
)
select id, parent, label
from roots
union
select id, parent, label
from @disc
where parent in (select id from roots)

Answer 2

看起来你并不想在这里递归。

你可以在应用程序中使用自连接。

select
row_number() over (order by isnull(c.parent, c.id), c.id),
c.id,
c.parent,
c.label
from @disc p
cross apply (
select p.id, p.parent, p.label
union all
select top 1 c.id, c.parent, c.label
from @disc c
where p.id = c.parent
order by c.id
) c
where p.parent is null;

,db&lt的在小提琴