Наглядный пример удобства использования CTE-выражений для решения задачи отбора данных с рекурсивным вычислением ("древовидный запрос").
Например, есть таблица работников, в которой работник и его руководитель оформлены следующим образом (см. табл. HumanResourses.Employee базы данных AdventureWorks):
Есть задача построить запрос, который возвратил бы список работников (EmployeeID) и их начальников (ManagerID), а также уровень подчиненности (SubmissionLevel) – как результат рекурсивного вычисления. Все согласно существующей древовидной структуре:
Как вариант решения такой задачи с использованием CTE-выражения:
USE AdventureWorks
GO
WITH CTE_Sample2 (EmployeeID, ManagerID, SubmissionLevel) AS
--EmployeeID - работник
--ManagerID - начальник
--SubmissionLevel - уровень подчинения
(
--запрос работников, которые никому не подчиняются
SELECT HighLevel.EmployeeID, HighLevel.ManagerID, 0 AS SubmissionLevel
FROM HumanResources.Employee HighLevel
WHERE ManagerID IS NULL
UNION ALL
--запрос работников, которые имеют начальников
SELECT LowLevel.EmployeeID, LowLevel.ManagerID, SubmissionLevel + 1
FROM HumanResources.Employee LowLevel
--JOIN к результату CTE-выражения
INNER JOIN CTE_Sample2 ON LowLevel.ManagerID = CTE_Sample2.EmployeeID
)
SELECT * FROM CTE_Sample2 ORDER BY SubmissionLevel
