SQL Server Rekursion select

Question

Ich habe zwei Tabellen:

entreprises(id, name)
entreprises_struct(id,entreprise_id, entreprise_child_id)

Nehmen wir an, ich habe diese Daten:

entreprises:
(1,canada)
(2,ontario)
(3,quebec)
(4,ottawa)
(5,toronto)
(6,montreal)
(7,laval)

entreprises_struct
(1,1,1)
(1,1,2)
(1,1,3)
(1,2,4)
(1,2,5)
(1,3,6)
(1,3,7)

Ich möchte eine Abfrage, die die Daten auf diese Weise sortiert:

montreal  (child level 3)
laval  (child level 3)
quebec (child level 2 and parent of those childs from level 3)
ottawa (child level 3)
toronto (child level 3)
ontario (child level 2 and parent of those childs from level 3)
canada (chil level 1 and parent of thoses childs from level 2)

Wenn ich das ab Stufe 7 hätte, müsste die Auswahl mit der Auflistung dieser Werte bis Stufe eins beginnen.

Ich kann CTE nicht verwenden, weil die Anzahl der Rekursionen zu sehr begrenzt ist.

Answer 1

Sie können verwenden option(maxrecursion 0) um die CTE-Rekursionsgrenze zu umgehen. Was den Sortierteil der Abfrage betrifft, siehe unten

Beispielhafte Daten

create table entreprises(id int, name varchar(max));
create table entreprises_struct(id int, entreprise_id int, entreprise_child_id int);
insert entreprises values
(1,'canada'),
(2,'ontario'),
(3,'quebec'),
(4,'ottawa'),
(5,'toronto'),
(6,'montreal'),
(7,'laval');
insert entreprises_struct values
(1,1,1),
(1,1,2),
(1,1,3),
(1,2,4),
(1,2,5),
(1,3,6),
(1,3,7);

Die Anfrage

;with cte as (
select entreprise_id, level=0,
    path=convert(varchar(max),entreprise_id) + '/'
from entreprises_struct
where entreprise_id =entreprise_child_id -- root
union all
select s.entreprise_child_id, cte.level+1,
    path=cte.path + convert(varchar(max),s.entreprise_child_id) + '/'
from entreprises_struct s
inner join cte on s.entreprise_id = cte.entreprise_id
where s.entreprise_child_id != cte.entreprise_id
)
select e.name
from cte
inner join entreprises e on e.id = cte.entreprise_id
order by path desc
option (maxrecursion 0)