Ответ
Обобщенные табличные выражения (Common Table Expressions, CTE) — это именованный временный результирующий набор, который определяется в рамках одного SQL-запроса (SELECT, INSERT, UPDATE или DELETE). Основная цель CTE — улучшение читаемости и структурирование сложных запросов.
Они объявляются с помощью ключевого слова WITH.
Основные преимущества:
- Читаемость: CTE позволяют разбить один монолитный и сложный запрос на несколько логических, последовательных шагов. Каждый шаг имеет свое имя, что делает логику запроса понятной.
- Многократное использование: На CTE можно ссылаться несколько раз в рамках основного запроса, избегая дублирования кода.
- Рекурсия: CTE — единственный стандартный способ выполнения рекурсивных запросов в SQL, что необходимо для работы с иерархическими данными (например, структура организации, дерево категорий).
Пример простого CTE:
-- Цель: Найти отделы, в которых работает более 10 сотрудников
-- Шаг 1: Определяем CTE для подсчета сотрудников в каждом отделе
WITH department_employee_count AS (
SELECT
department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
)
-- Шаг 2: Используем CTE для соединения с таблицей отделов и фильтрации
SELECT
d.department_name,
dec.employee_count
FROM departments d
JOIN department_employee_count dec ON d.id = dec.department_id
WHERE dec.employee_count > 10;Пример рекурсивного CTE:
-- Цель: Построить иерархию сотрудников, начиная с CEO
WITH RECURSIVE employee_hierarchy AS (
-- 1. Базовый случай: находим сотрудника верхнего уровня (CEO)
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 2. Рекурсивный шаг: присоединяем подчиненных к их менеджерам
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
-- Выводим всю построенную иерархию
SELECT * FROM employee_hierarchy;Ответ 18+ 🔞
Давай разберемся с этими вашими CTE, а то я смотрю, народ путается, как в тёмной комнате хуй с пальцем.
Вот представь, пишешь ты здоровенный SQL-запрос, страшный, как пиздопроебибна. В нём три JOIN, пять подзапросов и WHERE на полстраницы. Читать это — волнение ебать, терпения ноль ебать. Так вот, CTE — это как раз способ не сойти с ума. Это такой именованный кусок результата, который ты определяешь прямо в запросе через WITH, а потом используешь, как обычную таблицу.
Зачем это, блядь, надо?
- Читаемость, ёпта! Вместо одной каши ты разбиваешь логику на шаги. Сначала в одном CTE посчитал одно, в другом — другое, а в основном запросе всё это красиво собрал. Сразу видно, кто на ком стоял.
- Чтобы не повторяться. Определил CTE один раз — и юзай его в запросе хоть десять. Не надо копипастить один и тот же подзапрос, как последний распиздяй.
- Для рекурсии, сука! Это главная фишка. Хочешь развернуть дерево менеджеров или категорий? Без рекурсивного CTE — нихуя не получится стандартными средствами.
Простой пример, чтоб не охуеть сходу:
Допустим, надо найти отделы, где народу больше десяти. Делаем по шагам.
-- Шаг 1: Считаем, сколько в каждом отделе ртов.
WITH department_employee_count AS (
SELECT
department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
)
-- Шаг 2: Берём это, соединяем с таблицей отделов и отфильтровываем те, где счётчик больше 10.
SELECT
d.department_name,
dec.employee_count
FROM departments d
JOIN department_employee_count dec ON d.id = dec.department_id
WHERE dec.employee_count > 10;Видишь? Всё чисто, понятно. Сначала посчитали, потом отобрали. Никакой магии.
А теперь, блядь, рекурсия! Вот где начинается ёперный театр.
Допустим, у тебя таблица сотрудников, и у каждого есть manager_id. Надо вывести всю иерархию сверху донизу.
-- Включаем режим рекурсии, блядь!
WITH RECURSIVE employee_hierarchy AS (
-- 1. Базовый случай: ищем самого главного пацана (у него manager_id IS NULL).
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 2. Рекурсивный шаг: цепляем подчинённых к их начальникам.
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
-- Всё, иерархия готова, можно смотреть.
SELECT * FROM employee_hierarchy;Работает так: сначала находим CEO (базовый случай). Потом к нему UNION ALL'ом цепляем всех, у кого он в менеджерах. Потом к этим — их подчинённых, и так далее, пока цепочка не оборвётся. В итоге получается развёрнутое дерево с уровнями. Красота, а не запрос! Главное — рекурсию не уйти в бесконечную петлю, а то накроется медным тазом твой сервер.