SQL, или язык структурированных запросов, — это мощный инструмент для управления реляционными базами данных и манипулирования ими. Одной из особенно полезных функций SQL является возможность использования общих табличных выражений (CTE), также известных как «SQL с AS», для упрощения сложных запросов и улучшения читаемости кода. В этой статье мы рассмотрим различные методы использования CTE в SQL, приведя примеры кода для каждого метода. Независимо от того, являетесь ли вы новичком или опытным разработчиком SQL, это подробное руководство даст вам знания для эффективного использования CTE в операциях с базой данных.

Метод 1: базовый CTE
Базовый CTE определяется с помощью ключевого слова «WITH», за которым следует запрос и необязательный список столбцов. На результат CTE можно затем ссылаться как на таблицу в последующих запросах. Вот пример:

WITH my_cte AS (
  SELECT column1, column2
  FROM table1
  WHERE condition1
)
SELECT *
FROM my_cte
WHERE condition2;

Метод 2: рекурсивный CTE
Рекурсивный CTE полезен при работе с иерархическими или самоссылающимися данными. Они позволяют вам перемещаться по структуре данных, неоднократно запрашивая и опираясь на предыдущие результаты. Вот пример рекурсивного CTE, который извлекает иерархическую структуру отчетности сотрудника:

WITH RECURSIVE employee_hierarchy AS (
  SELECT employee_id, manager_id, employee_name
  FROM employees
  WHERE employee_id = '123'

  UNION ALL

  SELECT e.employee_id, e.manager_id, e.employee_name
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT *
FROM employee_hierarchy;

Метод 3: несколько CTE
Вы можете определить несколько CTE в одном запросе, что может быть полезно, когда вам нужно разбить сложную логику на более мелкие и более управляемые части. Каждый CTE отделяется запятой. Вот пример:

WITH cte1 AS (
  SELECT column1
  FROM table1
),
cte2 AS (
  SELECT column2
  FROM table2
)
SELECT *
FROM cte1, cte2
WHERE cte1.column1 = cte2.column2;

Метод 4: CTE с агрегированием
CTE также можно комбинировать с функциями агрегирования для выполнения вычислений над подмножествами данных. Это особенно полезно, когда вы хотите вычислить совокупные значения или выполнить сложные статистические вычисления. Вот пример:

WITH cte_sales AS (
  SELECT product_id, SUM(quantity) AS total_quantity
  FROM sales
  GROUP BY product_id
)
SELECT product_id, total_quantity, total_quantity / (SELECT SUM(total_quantity) FROM cte_sales) AS percentage
FROM cte_sales;

Метод 5: CTE с INSERT, UPDATE или DELETE
CTE можно использовать в сочетании с операторами INSERT, UPDATE или DELETE для эффективного управления данными. Это позволяет выполнять сложные операции на основе результатов CTE. Вот пример использования CTE с оператором INSERT:

WITH cte_new_customers AS (
  SELECT customer_id, customer_name
  FROM customers
  WHERE registration_date > '2023-01-01'
)
INSERT INTO new_customer_table (customer_id, customer_name)
SELECT customer_id, customer_name
FROM cte_new_customers;

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