Comment les auto-jointures récursives peuvent-elles récupérer efficacement des données hiérarchiques dans SQL Server ?

Récupération efficace de données hiérarchiques avec des auto-jointures récursives dans SQL Server

Les auto-jointures récursives de SQL Server fournissent une méthode efficace pour naviguer dans les structures de données hiérarchiques au sein d'une seule table. Cette technique consiste à parcourir les lignes du tableau pour collecter des données associées basées sur des relations hiérarchiques. La requête suivante démontre une approche simplifiée :

<code class="language-sql">WITH q AS (
    SELECT *
    FROM mytable
    WHERE ParentID IS NULL
    UNION ALL
    SELECT m.*
    FROM mytable m
    JOIN q ON m.parentID = q.PersonID
)
SELECT *
FROM q;</code>

Cette requête utilise une expression de table commune (CTE) récursive nommée « q ». Le CTE commence par sélectionner toutes les lignes où ParentID est NULL, représentant les nœuds de niveau supérieur dans la hiérarchie. Par la suite, il rejoint récursivement le mytable avec le CTE lui-même, en ajoutant des lignes enfants basées sur les colonnes ParentID et PersonID. Ce processus itératif se poursuit jusqu'à ce que tous les descendants soient inclus.

Considérez cet exemple mytable :

PersonID	Initials	ParentID
1	CJ	NULL
2	EB	1
3	MB	1
4	SW	2
5	YT	NULL
6	IS	5

Récupérer la hiérarchie de CJ (où PersonID = 1) donne :

PersonID	Initials	ParentID
1	CJ	NULL
2	EB	1
3	MB	1
4	SW	2

De même, la hiérarchie d'EB (PersonID = 2) renverrait :

PersonID	Initials	ParentID
2	EB	1
4	SW	2

Pour maintenir l'ordre hiérarchique, une requête modifiée intégrant une condition d'ordre est bénéfique :

<code class="language-sql">WITH q AS (
    SELECT m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
    FROM mytable m
    WHERE ParentID IS NULL
    UNION ALL
    SELECT m.*, q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
    FROM mytable m
    JOIN q ON m.parentID = q.PersonID
)
SELECT *
FROM q
ORDER BY bc;</code>

Cette requête améliorée introduit une colonne bc dans le CTE, représentant le chemin de chaque nœud vers la racine. Le classement des résultats par bc garantit que la structure hiérarchique, y compris l'ordre des frères et sœurs, est préservée. La clause ORDER BY peut être personnalisée pour ajuster l'ordre des frères et sœurs selon les besoins.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!