Comment puis-je effectuer des auto-jointures récursives dans SQL Server pour explorer des données hiérarchiques ?

Maîtriser les auto-jointures récursives dans SQL Server pour les données hiérarchiques

Les auto-jointures récursives sont essentielles pour naviguer dans les structures de données hiérarchiques dans les tables SQL Server. Cette technique est particulièrement utile lorsqu'il s'agit de données organisées en relations parents-enfants, comme des organigrammes ou des arbres généalogiques.

Une approche simple des auto-jointures récursives utilise une expression de table commune (CTE) :

<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 définit un CTE récursif, q. L'instruction SELECT initiale identifie les nœuds racines (ceux avec ParentID comme NULL). Le UNION ALL combine cela avec les instructions SELECT ultérieures qui joignent récursivement le CTE avec le mytable pour inclure tous les descendants. L'instruction SELECT finale récupère l'ensemble de données hiérarchiques complet.

Maintenir l'ordre hiérarchique

Pour préserver la structure hiérarchique et l'ordre d'origine, une requête modifiée est nécessaire :

<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 ajoute une colonne bc utilisant ROW_NUMBER() pour attribuer des identifiants uniques, en maintenant l'ordre au sein de chaque niveau de la hiérarchie. La clause ORDER BY bc garantit que le résultat final reflète la structure arborescente d'origine. L'ajustement des clauses ORDER BY dans la fonction ROW_NUMBER() permet de personnaliser l'ordre des nœuds frères.

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!