Comment récupérer une catégorie et ses ancêtres à l'aide de l'auto-jointure récursive dans SQL Server ?

Utilisation des auto-jointures récursives dans SQL Server pour récupérer les catégories et leurs ancêtres

Cet article montre comment récupérer une catégorie et ses catégories ancêtres à partir d'une table hiérarchique dans SQL Server à l'aide d'une auto-jointure récursive. Nous utiliserons une expression de table commune (CTE) pour cette tâche.

Scénario :

Considérons un tableau Categories avec des colonnes Id, Name et ParentId représentant une structure de catégories hiérarchique. L'objectif est d'écrire une requête qui renvoie une catégorie spécifique (par exemple, « Ordinateurs portables professionnels ») ainsi que toutes ses catégories parentes dans une seule chaîne séparée par des virgules.

Solution CTE récursive :

La solution utilise un CTE récursif pour parcourir la structure hiérarchique. Voici la requête :

<code class="language-sql">WITH CategoryHierarchy AS (
    SELECT id, name, CAST(name AS VARCHAR(MAX)) AS path, parent_id
    FROM Categories
    WHERE parent_id IS NULL  -- Start with root categories
    UNION ALL
    SELECT c.id, c.name, CAST(ch.path + ',' + c.name AS VARCHAR(MAX)), c.parent_id
    FROM Categories c
    INNER JOIN CategoryHierarchy ch ON c.parent_id = ch.id
)</code>

Ce CTE, CategoryHierarchy, joint récursivement la table Categories à elle-même. L'instruction SELECT initiale sélectionne les catégories racines (celles avec parent_id comme NULL). Le UNION ALL combine cela avec les sélections récursives ultérieures, construisant la chaîne path en concaténant les noms des catégories parent et enfant.

Requête pour récupérer une catégorie et des ancêtres spécifiques :

Pour récupérer la catégorie "Business Laptops" et ses ancêtres :

<code class="language-sql">SELECT id, name, path
FROM CategoryHierarchy
WHERE name = 'Business Laptops';</code>

Exemple de tableau et de données :

Créons un exemple de Categories table et insérons quelques données :

<code class="language-sql">CREATE TABLE Categories (
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    ParentId INT REFERENCES Categories(Id)
);

INSERT INTO Categories (Id, Name, ParentId)
VALUES
(1, 'Electronics', NULL),
(2, 'Laptops', 1),
(3, 'Desktops', 1),
(4, 'Business Laptops', 2),
(5, 'Gaming Laptops', 2);</code>

Résultat attendu :

Pour la requête ciblant les « ordinateurs portables professionnels », le résultat attendu serait :

<code>id      name                path
4       Business Laptops     Electronics,Laptops,Business Laptops</code>

Cette approche récupère efficacement une catégorie et sa lignée complète à l'aide d'un CTE récursif, fournissant une solution claire et efficace pour naviguer dans les données hiérarchiques dans SQL Server. N'oubliez pas d'ajuster la longueur VARCHAR(MAX) si les noms de vos catégories risquent de dépasser cette limite.

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!