Обобщённые табличные выражения (WITH)
Дайте строительному блоку запроса имя и читайте SQL сверху вниз, как рецепт
Главная идея: Обобщённое табличное выражение (CTE) — это временный именованный набор строк, задаваемый ключевым словом
WITH в начале инструкции. Вместо того чтобы прятать подзапрос глубоко внутри другого запроса, вы один раз даёте ему имя и затем обращаетесь к этому имени как к обычной таблице. Одна инструкция может задавать несколько CTE, и каждый следующий может опираться на предыдущие, превращая запутанное гнездо скобок в понятный упорядоченный конвейер.
К концу темы вы сможете:
- задать один CTE через
WITH name AS (SELECT ...)и обратиться к нему, - соединить несколько CTE в читаемый многошаговый конвейер,
- объяснить, почему CTE читается легче, чем та же логика во вложенном подзапросе,
- ссылаться на один CTE из другого и из финального запроса,
- сформулировать правила области видимости: сколько живёт CTE и что его видит.
0. Наша таблица-пример
Все запросы на этой странице используют одну небольшую таблицу sales. В ней одна строка на каждую проданную книгу: продавец, его регион, название и сумма продажи в долларах. Вот как она создаётся и заполняется:
CREATE TABLE sales (
id INT PRIMARY KEY,
seller VARCHAR(30),
region VARCHAR(20),
book VARCHAR(30),
amount INT
);
INSERT INTO sales (id, seller, region, book, amount) VALUES
(1, 'Ann', 'North', 'SQL Basics', 40),
(2, 'Ann', 'North', 'Joins Deep', 55),
(3, 'Bo', 'North', 'SQL Basics', 40),
(4, 'Cy', 'South', 'Indexes 101', 30),
(5, 'Cy', 'South', 'SQL Basics', 40),
(6, 'Di', 'East', 'Joins Deep', 55),
(7, 'Di', 'East', 'Indexes 101', 30),
(8, 'Ann', 'North', 'Indexes 101', 30);Вся таблица выглядит так:
| id | seller | region | book | amount |
|---|---|---|---|---|
| 1 | Ann | North | SQL Basics | 40 |
| 2 | Ann | North | Joins Deep | 55 |
| 3 | Bo | North | SQL Basics | 40 |
| 4 | Cy | South | Indexes 101 | 30 |
| 5 | Cy | South | SQL Basics | 40 |
| 6 | Di | East | Joins Deep | 55 |
| 7 | Di | East | Indexes 101 | 30 |
| 8 | Ann | North | Indexes 101 | 30 |
Запомните эти суммы. По трём регионам получается North = 165 (строки 1, 2, 3, 8), South = 70 (строки 4, 5) и East = 85 (строки 6, 7). Скоро мы вычислим эти итоги через CTE.