Note
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de modifier les répertoires.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de changer de répertoire.
S’applique à :
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Base de données SQL dans Microsoft Fabric
Une expression de table commune (CTE) offre l’avantage significatif de pouvoir se référencer elle-même, créant ainsi un CTE récursif. Un CTE récursif est celui dans lequel un CTE initial est exécuté à plusieurs reprises pour retourner des sous-ensembles de données jusqu’à ce que le jeu de résultats complet soit obtenu.
Une requête est appelée requête récursive lorsqu’elle fait référence à un CTE récursif. Le retour de données hiérarchiques est une utilisation courante des requêtes récursives. Par exemple, l’affichage des employés dans un graphique organisationnel ou des données dans un scénario de facture de matériaux dans lequel un produit parent possède un ou plusieurs composants et que ces composants peuvent avoir des sous-composants, ou être des composants, d’autres parents.
Une analyse CTE récursive peut simplifier considérablement le code requis pour exécuter une requête récursive dans une instruction, ou SELECTINSERTUPDATEun , DELETECREATE VIEW Dans les versions antérieures de SQL Server, une requête récursive nécessite généralement l’utilisation de tables temporaires, de curseurs et de logiques pour contrôler le flux des étapes récursives. Pour plus d’informations sur les expressions de table courantes, consultez WITH common_table_expression.
Dans Microsoft Fabric, Fabric Data Warehouse et le point de terminaison d’analytique SQL prennent tous deux en charge les CTEs standard, séquentielles et imbriquées, mais pas les CTEs récursives.
Structure d’un CTE récursif
La structure de l’te CTE récursive dans Transact-SQL est similaire aux routines récursives dans d’autres langages de programmation. Bien qu’une routine récursive dans d’autres langages retourne une valeur scalaire, une CTE récursive peut retourner plusieurs lignes.
Un CTE récursif se compose de trois éléments :
Appel de la routine.
Le premier appel de l’octet CTE récursif se compose d’une ou plusieurs définitions de requête CTE jointes par
UNION ALL,UNIONouEXCEPTINTERSECTopérateurs. Étant donné que ces définitions de requête forment le jeu de résultats de base de la structure CTE, elles sont appelées membres d’ancre.Les définitions de requête CTE sont considérées comme des membres d’ancrage, sauf s’ils référencent l’objet CTE lui-même. Toutes les définitions de requête de membre d’ancre doivent être positionnées avant la première définition de membre récursive, et un
UNION ALLopérateur doit être utilisé pour joindre le dernier membre d’ancrage au premier membre récursif.Appel récursif de la routine.
L’appel récursif inclut une ou plusieurs définitions de requête CTE jointes par
UNION ALLdes opérateurs qui référencent l’objet CTE lui-même. Ces définitions de requête sont appelées membres récursifs.Vérification de l’arrêt.
La vérification de l’arrêt est implicite ; la récursivité s’arrête lorsqu’aucune ligne n’est retournée par l’appel précédent.
Note
Une CTE récursive incorrectement composée peut entraîner une boucle infinie. Par exemple, si la définition de requête du membre récursif renvoie les mêmes valeurs pour les colonnes parent et enfant, une boucle infinie est créée. Lors du test des résultats d’une requête récursive, vous pouvez limiter le nombre de niveaux de récursivité autorisés pour une instruction spécifique à l’aide de l’indicateur MAXRECURSION et d’une valeur comprise entre 0 et 32 767 dans la clause du , OPTION, INSERTou UPDATE de l’instruction DELETESELECT.
Pour plus d’informations, consultez :
Pseudocode et sémantique
La structure CTE récursive doit contenir au moins un membre d’ancrage et un membre récursif. Le pseudocode suivant montre les composants d’une CTE récursive simple qui contient un seul membre d’ancrage et un seul membre récursif.
WITH cte_name ( column_name [ ,...n ] )
AS
(
CTE_query_definition -- Anchor member is defined.
UNION ALL
CTE_query_definition -- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
La sémantique de l’exécution récursive est la suivante :
- Fractionnez l’expression CTE en membres ancres et récursifs.
- Exécutez les membres d’ancrage qui créent le premier appel ou jeu de résultats de base (
T0). - Exécutez les membres récursifs avec
Ticomme entrée etTi+ 1 comme sortie. - Répétez l’étape 3 jusqu’à ce qu’un jeu vide soit retourné.
- Retournez le jeu de résultats. C’est un
UNION ALLdeT0.Tn
Examples
L’exemple suivant montre la sémantique de la structure CTE récursive en retournant une liste hiérarchique d’employés, en commençant par l’employé de classement le plus élevé, dans la AdventureWorks2025 base de données. Une procédure pas à pas de l’exécution du code suit l’exemple.
Créez une table d’employés :
CREATE TABLE dbo.MyEmployees
(
EmployeeID SMALLINT NOT NULL,
FirstName NVARCHAR (30) NOT NULL,
LastName NVARCHAR (40) NOT NULL,
Title NVARCHAR (50) NOT NULL,
DeptID SMALLINT NOT NULL,
ManagerID INT NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
Remplissez la table avec des valeurs :
INSERT INTO dbo.MyEmployees
VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL),
(273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1),
(274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273),
(275, N'Michael', N'Blythe', N'Sales Representative', 3, 274),
(276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274),
(285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273),
(286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285),
(16, N'David', N'Bradley', N'Marketing Manager', 4, 273),
(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);
USE AdventureWorks2008R2;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS (
-- Anchor member definition
SELECT e.ManagerID,
e.EmployeeID,
e.Title,
edh.DepartmentID,
0 AS Level
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID
AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID,
e.EmployeeID,
e.Title,
edh.DepartmentID,
Level + 1
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID
AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID)
-- Statement that executes the CTE
SELECT ManagerID,
EmployeeID,
Title,
DeptID,
Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing'
OR Level = 0;
GO
Exemple de procédure pas à pas du code
L’objet CTE récursif, DirectReportsdéfinit un membre d’ancrage et un membre récursif.
Le membre d’ancrage retourne le jeu T0de résultats de base . Il s’agit de l’employé de classement le plus élevé de l’entreprise. Autrement dit, un employé qui ne signale pas à un responsable.
Voici le jeu de résultats retourné par le membre d’ancrage :
ManagerID EmployeeID Title Level
--------- ---------- ----------------------------- ------
NULL 1 Chief Executive Officer 0
Le membre récursif retourne les subordonnés directs de l’employé dans le jeu de résultats du membre d’ancrage. Pour ce faire, une opération de jointure est effectuée entre la table Employee et l’instance DirectReports CTE. Il s’agit de cette référence au CTE lui-même qui établit l’appel récursif. En fonction de l’employé de l’instance CTE DirectReports en tant qu’entrée (Ti), la jointure (MyEmployees.ManagerID = DirectReports.EmployeeID) retourne comme sortie (Ti + 1), les employés qui ont (Ti) en tant que responsable.
Par conséquent, la première itération du membre récursif retourne ce jeu de résultats :
ManagerID EmployeeID Title Level
--------- ---------- ----------------------------- ------
1 273 Vice President of Sales 1
Le membre récursif est activé à plusieurs reprises. La deuxième itération du membre récursif utilise le jeu de résultats à ligne unique à l’étape 3 (contenant un EmployeeID ) 273comme valeur d’entrée et retourne ce jeu de résultats :
ManagerID EmployeeID Title Level
--------- ---------- ----------------------------- ------
273 16 Marketing Manager 2
273 274 North American Sales Manager 2
273 285 Pacific Sales Manager 2
La troisième itération du membre récursif utilise le jeu de résultats précédent comme valeur d’entrée et retourne ce jeu de résultats :
ManagerID EmployeeID Title Level
--------- ---------- ----------------------------- ------
16 23 Marketing Specialist 3
274 275 Sales Representative 3
274 276 Sales Representative 3
285 286 Sales Representative 3
Le jeu de résultats final retourné par la requête en cours d’exécution est l’union de tous les jeux de résultats générés par l’ancre et les membres récursifs.
Voici l'ensemble des résultats.
ManagerID EmployeeID Title Level
--------- ---------- ----------------------------- ------
NULL 1 Chief Executive Officer 0
1 273 Vice President of Sales 1
273 16 Marketing Manager 2
273 274 North American Sales Manager 2
273 285 Pacific Sales Manager 2
16 23 Marketing Specialist 3
274 275 Sales Representative 3
274 276 Sales Representative 3
285 286 Sales Representative 3