Основы CTE в SQL - синтаксис выражения

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

Основы CTE

CTE — это временная таблица, определенная в самом запросе, которая существует только в рамках этого запроса. CTE создается с помощью ключевого слова WITH.

Синтаксис CTE

Базовый синтаксис для определения и использования CTE выглядит следующим образом:

WITH CTE_Name AS (
    -- Определение CTE: запрос, который создает временный результат
    SELECT column1, column2, ...
    FROM table_name
    WHERE conditions
)
-- Основной запрос, использующий CTE
SELECT column1, column2, ...
FROM CTE_Name
WHERE additional_conditions;

Объяснение:

1. WITH CTE_Name AS (...): Определяет CTE с именем CTE_Name. Внутри скобок пишется SQL-запрос, который создает временный результат.

2. Основной запрос: Использует CTE как временную таблицу, к которой можно обращаться так же, как и к любой другой таблице в запросе.

Примеры использования CTE

Пример 1: Простой CTE

Предположим, у нас есть таблица employees, и мы хотим найти всех сотрудников с зарплатой выше 50,000.

WITH HighSalaryEmployees AS (
    SELECT employee_id, name, salary
    FROM employees
    WHERE salary > 50000
)
SELECT *
FROM HighSalaryEmployees;

В этом примере HighSalaryEmployees — это CTE, который содержит сотрудников с зарплатой выше 50,000. Основной запрос выбирает все строки из этого CTE.

Пример 2: Множественные CTE

Можно определить несколько CTE и использовать их в основном запросе.

WITH HighSalaryEmployees AS (
    SELECT employee_id, name, salary
    FROM employees
    WHERE salary > 50000
),
LowSalaryEmployees AS (
    SELECT employee_id, name, salary
    FROM employees
    WHERE salary <= 50000
)
SELECT *
FROM HighSalaryEmployees
UNION ALL
SELECT *
FROM LowSalaryEmployees;

Здесь мы определяем два CTE: HighSalaryEmployees и LowSalaryEmployees, и затем объединяем результаты с помощью UNION ALL.

Пример 3: CTE с агрегатными функциями

CTE можно использовать для предварительной агрегации данных.

WITH DepartmentSalaries AS (
    SELECT department_id, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.department_id, d.average_salary, e.name, e.salary
FROM DepartmentSalaries d
JOIN employees e ON d.department_id = e.department_id
WHERE e.salary > d.average_salary;

В этом примере CTE DepartmentSalaries вычисляет среднюю зарплату по каждому отделу, а основной запрос выбирает сотрудников, чья зарплата выше средней зарплаты в их отделе.

Пример 4: Рекурсивные CTE

Рекурсивные CTE позволяют работать с иерархическими данными. Вот пример иерархической структуры сотрудников.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Member: Начинаем с сотрудников, у которых нет руководителя
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive Member: Находим подчиненных для каждого сотрудника
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT *
FROM EmployeeHierarchy;

В этом примере EmployeeHierarchy создается рекурсивно: сначала выбираются сотрудники без руководителей (anchor member), затем рекурсивно добавляются их подчиненные (recursive member).

Заключение

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