Что такое CTE (Common Table Expression) в SQL?

CTE (Common Table Expression, обобщенное табличное выражение) — это временный именованный результирующий набор, который определяется в рамках выполнения одного SQL-запроса. CTE улучшает читаемость и структурированность сложных запросов, особенно тех, что требуют рекурсии.

Основные преимущества:

• Улучшенная читаемость: Позволяет разбить сложный запрос на логические блоки.
• Рекурсивные запросы: CTE — основной способ выполнения рекурсивных запросов в SQL.
• Замена вложенных подзапросов: Часто делает код чище, чем вложенные SELECT.

Базовый синтаксис:

WITH MyCTE_Name (Column1, Column2) AS (
    -- Определение CTE
    SELECT Id, Name FROM Employees WHERE DepartmentId = 1
)
-- Основной запрос, использующий CTE
SELECT * FROM MyCTE_Name WHERE Name LIKE 'A%';

Пример использования в C# с Dapper (нерекурсивный CTE):

var sql = @"
WITH HighValueOrders AS (
    SELECT OrderId, CustomerId, TotalAmount
    FROM Orders
    WHERE TotalAmount > 1000 AND YEAR(OrderDate) = 2023
)
SELECT c.Name, hvo.TotalAmount
FROM HighValueOrders hvo
JOIN Customers c ON hvo.CustomerId = c.Id";

using var connection = new SqlConnection(connectionString);
var results = await connection.QueryAsync(sql);

Рекурсивный CTE (пример: построение иерархии сотрудников):

WITH EmployeeHierarchy AS (
    -- Якорная часть: начальная точка (руководители верхнего уровня)
    SELECT Id, Name, ManagerId, 1 AS Level
    FROM Employees
    WHERE ManagerId IS NULL

    UNION ALL

    -- Рекурсивная часть: присоединение подчиненных
    SELECT e.Id, e.Name, e.ManagerId, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerId = eh.Id
)
SELECT * FROM EmployeeHierarchy
ORDER BY Level, Name;

Важно: CTE существует только во время выполнения запроса. Для повторного использования в рамках сессии лучше подойдет временная таблица (#TempTable).