Comment utiliser l'auto-jointure récursive dans SQL Server pour afficher les ancêtres des données hiérarchiques ?

Astuce pour utiliser l'auto-jointure récursive pour représenter des données hiérarchiques dans SQL Server

Supposons que vous ayez une table appelée « Catégories » avec les colonnes Id, Name et ParentId, permettant la création de hiérarchies illimitées au sein des catégories. Pour afficher la catégorie Ordinateurs portables professionnels et toutes ses catégories ancêtres, vous pouvez tirer parti de la puissance des expressions de table communes (CTE) récursives.

Créons un exemple de tableau « Catégories » et remplissons-le avec des données pour illustrer la solution :

<code class="language-sql">DECLARE @Categories AS TABLE
(
    Id INT,
    Name VARCHAR(100),
    ParentId INT
);

INSERT INTO @Categories VALUES
(1, 'A', NULL),
(2, 'A.1', 1),
(3, 'A.2', 1),
(4, 'A.1.1', 2),
(5, 'B', NULL),
(6, 'B.1', 5),
(7, 'B.1.1', 6),
(8, 'B.2', 5),
(9, 'A.1.1.1', 4),
(10, 'A.1.1.2', 4);</code>

Ensuite, nous créons un CTE récursif appelé « PathFinder » pour parcourir les relations parent-enfant et concaténer les noms d'ancêtres à la colonne « Path » :

<code class="language-sql">WITH PathFinder AS
(
    SELECT Id, Name, Name AS Path, ParentId
    FROM @Categories 
    WHERE ParentId IS NULL
    UNION ALL
    SELECT t.Id, t.Name, CAST(cf.Path + ', ' + t.Name AS VARCHAR(100)), t.ParentId
    FROM @Categories t
    INNER JOIN PathFinder cf ON t.ParentId = cf.Id
)</code>

Enfin, on peut utiliser le CTE PathFinder pour récupérer le nom de la catégorie "Business Laptops" et ses ancêtres :

<code class="language-sql">SELECT Name, Path
FROM PathFinder
WHERE Name = 'Business Laptops';</code>

Cette requête renverra le nom de la catégorie et ses ancêtres séparés par des virgules.

Voici une répartition des requêtes récursives :

• Cas de base : La première partie du CTE sélectionne une catégorie qui n'a pas de parent (c'est-à-dire le nœud racine) et initialise son "Chemin" avec son nom.
• Cas récursif : La deuxième partie joint récursivement les catégories à leurs parents et ajoute leurs noms à la colonne "Chemin". Ce processus se poursuivra jusqu'à ce qu'il n'y ait plus de catégories non traitées.
• Résultat : CTE génère une séquence de lignes, chacune représentant une catégorie et ses ancêtres complets. En sélectionnant le nom de catégorie requis dans ce CTE, nous pouvons récupérer les données requises.

Grâce à cette approche récursive, vous pouvez représenter et interroger efficacement des données hiérarchiques dans SQL Server, ce qui constitue une technique précieuse pour gérer et analyser les données avec des relations inhérentes.

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!