T-SQL - Zapytanie zwracające drzewo

0

Walczę cały dzień i nie mogę sobie poradzić. Jest sobie taka tabela:

+-------+-----------+-----------+
| Id	| Number	| Parent	|
+-------+-----------+-----------+
| 1		| 1			| 0			|
+-------+-----------+-----------+
| 2		| 2			| 0			|
+-------+-----------+-----------+
| 3		| 3			| 0			|
+-------+-----------+-----------+
| 4		| 1.1		| 1			|
+-------+-----------+-----------+
| 5		| 1.2		| 1			|
+-------+-----------+-----------+
| 6		| 2.1		| 2			|
+-------+-----------+-----------+
| 7		| 3.1		| 3			|
+-------+-----------+-----------+
| 8		| 3.2		| 3			|
+-------+-----------+-----------+
| 9		| 3.3		| 3			|
+-------+-----------+-----------+
| 10	| 1.2.1		| 5			|
+-------+-----------+-----------+
| 11	| 1.2.2		| 5			|
+-------+-----------+-----------+
| 12	| 1.2.3		| 5			|
+-------+-----------+-----------+
| 13	| 3.2.1		| 8			|
+-------+-----------+-----------+
| 14	| 3.2.2		| 8			|
+-------+-----------+-----------+
| 15	| 3.2.2.1 	| 14		|
+-------+-----------+-----------+

Potrzebuję wydobyć z niej dane w formie zgodnej z hierarchią, czyli:

+-------+-----------+-----------+-------+
| Id	| Name		| Parent	| Lvl	|
+-------+-----------+-----------+-------+
| 1  	| 1 		|	0		| 0		|
+-------+-----------+-----------+-------+
| 4  	| 1.1 		|	1		| 1		|
+-------+-----------+-----------+-------+
| 5  	| 1.2 		|	1		| 1		|
+-------+-----------+-----------+-------+
| 10 	| 1.2.1 	|	5		| 2		|
+-------+-----------+-----------+-------+
| 11 	| 1.2.2 	|	5		| 2		|
+-------+-----------+-----------+-------+
| 12 	| 1.2.3 	|	5		| 2		|
+-------+-----------+-----------+-------+
| 2  	| 2 		|	0		| 0		|
+-------+-----------+-----------+-------+
| 6  	| 2.1 		|	2		| 1		|
+-------+-----------+-----------+-------+
| 3  	| 3 		|	0		| 0		|
+-------+-----------+-----------+-------+
| 7  	| 3.1 		|	3		| 1		|
+-------+-----------+-----------+-------+
| 8  	| 3.2 		|	3		| 1		|
+-------+-----------+-----------+-------+
| 13 	| 3.2.1 	|	8		| 2		|
+-------+-----------+-----------+-------+
| 14 	| 3.2.2 	|	8		| 2		|
+-------+-----------+-----------+-------+
| 15 	| 3.2.2.1 	|	14		| 3		|
+-------+-----------+-----------+-------+
| 9  	| 3.3 		|	3		| 1		|
+-------+-----------+-----------+-------+

Poszukałem, pokopałem, stworzyłem takie coś:

WITH recursive_CTE
AS
(
	SELECT Id, Name, Parent, 0 AS Lvl 
        FROM test
	WHERE Parent = 0
	UNION ALL
	SELECT T.Id, T.Name, T.Parent, Lvl + 1
	FROM test T INNER JOIN recursive_CTE R 
             ON T.Parent = R.Id
)
SELECT * FROM recursive_CTE

W wyniku dostaję to:

+-------+-----------+-----------+-------+
| Id	| Name		| Parent	| Lvl	|
+-------+-----------+-----------+-------+
| 1		| 1			| 0			| 0		|
+-------+-----------+-----------+-------+
| 2		| 2			| 0			| 0		|
+-------+-----------+-----------+-------+
| 3		| 3			| 0			| 0		|
+-------+-----------+-----------+-------+
| 7		| 3.1		| 3			| 1		|
+-------+-----------+-----------+-------+
| 8		| 3.2		| 3			| 1		|
+-------+-----------+-----------+-------+
| 9		| 3.3		| 3			| 1		|
+-------+-----------+-----------+-------+
| 13	| 3.2.1		| 8			| 2		|
+-------+-----------+-----------+-------+
| 14	| 3.2.2		| 8			| 2		|
+-------+-----------+-----------+-------+
| 15	| 3.2.2.1	| 14		| 3		|
+-------+-----------+-----------+-------+
| 6		| 2.1		| 2			| 1		|
+-------+-----------+-----------+-------+
| 4		| 1.1		| 1			| 1		|
+-------+-----------+-----------+-------+
| 5		| 1.2		| 1			| 1		|
+-------+-----------+-----------+-------+
| 10	| 1.2.1		| 5			| 2		|
+-------+-----------+-----------+-------+
| 11	| 1.2.2		| 5			| 2		|
+-------+-----------+-----------+-------+
| 12	| 1.2.3		| 5			| 2		|
+-------+-----------+-----------+-------+

Czyli nie do końca ta kolejność której potrzebuję. Jak to ugryźć żeby wynik był zgodnie z zagłębieniem?

0

Nie sortujesz (ORDER BY) to trudno zakładać, że wynik będzie zgodny z jakimś porządkiem, który po cichu zakładasz. Co to znaczy "zgodnie z zagłębieniem"?

0

Po co wynajdujesz kolo na nowo?
Rozwiazania sa dwa:

  • stosujesz zapytania rekurencyjne wspierane przez konkretny DBMS
  • stosujesz uniwersalne rozwiazanie Depesza.

To drugie nie mam pod reka ale bardzo polecam.

0

Domyślam się, że powinienem wynik posortować :) NIe wiem, jak to zrobić. Chciałbym, żeby wynik wyświetlał zgodnie z zagłębieniem, czyli po każdym rodzicu żeby wyświetlało się jego dziecko/dzieci, czyli tak:

1      
1.1    
1.2    
1.2.1  
1.2.2  
1.2.3  
2      
2.1    
3      
3.1    
3.2    
3.2.1  
3.2.2  
3.2.2.1
3.3
0

Tak na szybko to zrób sobie jeszcze jedną kolumnę np. NAME_LEX w której doklejaj wiodące 0 do określonej długości i posortuj po takiej kolumnie leksykograficznie.
Minus jest taki, że musisz znać jakieś max. długość identyfikatora ID... ;)

001      
001.001    
001.002    
001.002.001  
001.002.002  
..
011
itd.
0

Dodanie order by rozwiąże Twój problem:

WITH recursive_CTE
AS
(
    SELECT Id, Name, Parent, 0 AS Lvl 
        FROM test
    WHERE Parent = 0
    UNION ALL
    SELECT T.Id, T.Name, T.Parent, Lvl + 1
    FROM test T INNER JOIN recursive_CTE R 
             ON T.Parent = R.Id
)
SELECT * FROM recursive_CTE order by name

Oczywiście weź pod uwage sugestie @yarel bo jak będziesz miał 10 poziom to kolejność będzie taka

1      
1.1    
1.2    
1.2.1  
1.2.2  
1.2.3  
10
10.1
2      
2.1    
3      
3.1    
3.2    
3.2.1  
3.2.2  
3.2.2.1
3.3

Generalnie od wersji 2008 sql ma hierarchyid co znacząco upraszcza operacje na drzewach i sort jest łatwiejszy: https://www.mssqltips.com/sqlservertip/6048/sql-server-hierarchyid-data-type-overview-and-examples/

0

Sortowanie po nazwie nie rozwiązuje problemu, bo kolumna Name zawiera 'normalne' dane, które posortowane alfabetycznie nie oddają kompletnie zależności rodziców i dzieci. Tutaj są wstawione przykładowe, po to żeby pokazać hierarchię.

Najgorsze że folmularz który ma obsługiwać te dane musi być zrobiony w Base, a ten mam nieodparte wrażenie, polegnie z kretesem na rzeczach pokroju hierarchyid :(

0

No to dodaj sieżke w tym cte:

WITH recursive_CTE AS (
  SELECT 
    Id, 
    Name, 
    Parent, 
    0 AS Level, 
    CONVERT(VARCHAR(300), RIGHT('00000' + CONVERT(VARCHAR(5), id), 5)) poziom
  FROM 
    test
  WHERE
    Parent = 0
  UNION ALL
  SELECT 
    T.Id, 
    T.Name, 
    T.Parent, 
    Level + 1, 
    CONVERT(VARCHAR(300), poziom + '.' + RIGHT('00000' + CONVERT(VARCHAR(5), t.id), 5))
  FROM 
    test T
    INNER JOIN recursive_CTE R ON T.Parent = R.Id;
)
SELECT * FROM recursive_CTE order by poziom
0

OK, wszystko jasne, dzięki serdeczne :) Sporo się jeszcze muszę nauczyć...

1 użytkowników online, w tym zalogowanych: 0, gości: 1