Занятие 26. Табличные выражения

📁 Блок: SQL / MySQL / Подзапросы и CTE ⏱️ Время изучения: ~75 мин 🎯 Сложность: Средняя

#as #select #from

⚡ Кратко: команды

WITH имя AS (SELECT ...) SELECT ... FROM имя;
CTE можно использовать в FROM и внутри WHERE ... IN (SELECT ... FROM имя).
Один запрос может содержать несколько CTE через запятую.

🔖 Справочник операторов

Базовый CTE

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE conditions
)
SELECT *
FROM cte_name;

CTE для агрегации

WITH product_summary AS (
    SELECT product_id,
           COUNT(*) AS total_orders,
           SUM(unit_price * quantity) AS total_revenue
    FROM order_details
    GROUP BY product_id
)
SELECT p.product_name, ps.total_orders, ps.total_revenue
FROM product_summary AS ps
JOIN products AS p ON ps.product_id = p.id
ORDER BY ps.total_orders DESC
LIMIT 10;

CTE, используемый несколько раз

WITH avg_price AS (
    SELECT AVG(unit_price) AS ap
    FROM order_details
)
SELECT *
FROM order_details
WHERE unit_price > (SELECT ap FROM avg_price)
  AND unit_price < (SELECT ap * 1.5 FROM avg_price);

CTE для фильтрации через IN

WITH sales_repr AS (
    SELECT id
    FROM employees
    WHERE job_title = 'Sales Representative'
)
SELECT *
FROM orders
WHERE employee_id IN (SELECT id FROM sales_repr);

Несколько CTE в одном запросе

WITH cte1 AS (
    SELECT id FROM customers WHERE city = 'Los Angeles'
),
cte2 AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
)
SELECT c.id, cte2.order_count
FROM cte1 AS c
JOIN cte2 ON c.id = cte2.customer_id;

🔗 Полный справочник

Более полный каталог SQL-операторов курса — в справочнике SQL.