Requêtes SQL récursives
Voici une nouvelle fonctionnalité qu'offre Microsoft SQL 2005 et que je viens de découvrir : les requêtes récursives grâce aux "Common Table Expressions" (CTE).
Ces requêtes permettent de manipuler des hierarchies de données (Parent => Enfants) comme des catégories > sous-catégories > ... > sous-catégories.
La syntaxe est la suivante :
WITH cte_name ( column_name [,...n] )
AS
(
Requête de définition
UNION ALL
Requête récursive
)
Requête utilisant cte_name
La requête de définition reprend les colonnes spécifiées en argument de la CTE et décrit l'élément parent, l'élément de départ de la requête.
La requête récursive permet de définir la manière d'accéder aux enfants. Pour la construire, il suffit de faire référence à la CTE comme s'il sagissait d'une table ou d'une vue. Voici un exemple tiré de la MSDN :
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID 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 HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
GO
Pour plus de détails, voici la référence dans la MSDN :
Source : http://msdn2.microsoft.com/en-us/library/ms186243.aspx