Ответ
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).
Ответ 18+ 🔞
Смотри, вот тебе про CTE, но так, чтобы не уснуть на третьем предложении.
Это, блядь, как будто ты пишешь запрос, а внутри него создаёшь временную табличку прямо на лету, чтобы не засорять основной код кучей подзапросов. Представь, что ты готовишь сложное блюдо. Вместо того чтобы все ингредиенты валить в одну кастрюлю и мешать до состояния говна, ты сначала на отдельной доске красиво нарежешь лук, морковку — вот это и есть CTE. Потом это аккуратно в основное блюдо отправляешь. И читать приятно, и понять, что происходит, легче.
Вот смотри, как это выглядит, простейший случай:
WITH ОтборныеЗаказы AS (
SELECT IdЗаказа, IdКлиента, Сумма
FROM Заказы
WHERE Сумма > 1000 AND YEAR(ДатаЗаказа) = 2023
)
SELECT к.Имя, оз.Сумма
FROM ОтборныеЗаказы оз
JOIN Клиенты к ON оз.IdКлиента = к.Id;Видишь? Сначала в блоке WITH мы как бы говорим: «Так, блядь, стоп. Давай-ка сначала выберем все заказы за 2023 год, где сумма больше тысячи, и назовём этот временный результат "ОтборныеЗаказы"». А потом в основном запросе просто берём и работаем с этим «ОтборныеЗаказы», как с обычной таблицей. Чисто, аккуратно, мозг не ебёт.
А теперь, ёпта, самое интересное — рекурсия. Вот где CTE реально пашет. Допустим, у тебя таблица сотрудников, где у каждого есть ссылка на начальника (ManagerId). И тебе надо получить всю иерархию подчинения, от самого верхнего босса до самого последнего стажёра. Без CTE это пиздец и боль, а с ним — красота.
WITH ИерархияСотрудников AS (
-- Якорь: берём тех, у кого нет начальника (самые главные)
SELECT Id, Имя, ManagerId, 1 AS Уровень
FROM Сотрудники
WHERE ManagerId IS NULL
UNION ALL -- А вот тут магия начинается
-- Рекурсивная часть: цепляем подчинённых к начальникам
SELECT с.Id, с.Имя, с.ManagerId, и.Уровень + 1
FROM Сотрудники с
INNER JOIN ИерархияСотрудников и ON с.ManagerId = и.Id
)
SELECT * FROM ИерархияСотрудников
ORDER BY Уровень, Имя;Объясняю на пальцах, как для дебила (без обид). Сначала запрос берёт всех, у кого ManagerId IS NULL — это наши царьки, первый уровень. Потом он говорит: «А теперь, сука, найдите всех, чей ManagerId совпадает с Id тех, кого мы только что нашли». И этот процесс повторяется снова и снова, UNION ALL склеивает результаты, пока не закончатся подчинённые. В итоге получается полная развёртка дерева. Гениально и просто, как тапок.
Но запомни одну важную вещь, чтобы потом не орать «почему не работает!». CTE — это как призрак. Он существует ТОЛЬКО на время выполнения этого одного запроса. Выполнился запрос — и CTE испарился, его больше нет. Если тебе нужно что-то переиспользовать в рамках всей сессии или между кучей запросов — тогда тебе не CTE, а временная таблица (#TempTable), это совсем другая история.
Короче, CTE — это твой друг, когда запрос превращается в нечитаемую простыню. Разбил его на логические куски через WITH, назвал их человеческими именами — и жить стало легче, жить стало веселее. А рекурсивные CTE вообще из разряда чёрной магии, которая решает задачи, от которых без них волосы бы на жопе седели.
