Рекурсивные CTE и иерархические данные
Запросы к деревьям, оргструктурам и путям с помощью запроса, который ссылается сам на себя
Главная идея: рекурсивное обобщённое табличное выражение (CTE) — это именованный запрос, который может ссылаться сам на себя, позволяя одному оператору SQL пройти иерархию неизвестной глубины. Вы пишете якорную часть (начальные строки) и рекурсивную часть (строки, полученные из предыдущего шага), склеиваете их через
UNION ALL, и движок продолжает итерации, пока рекурсивная часть не вернёт ни одной строки. Именно так разворачивают оргструктуру, дерево категорий или трассируют путь от узла до корня, не зная заранее, сколько уровней существует.
К концу урока вы сможете:
- читать анатомию
WITH RECURSIVE— якорная и рекурсивная части, соединённыеUNION ALL, - обходить таблицу родитель/потомок (оргструктуру, дерево категорий) сверху вниз и снизу вверх,
- отслеживать уровень глубины и строить строку пути при спуске,
- ограничивать рекурсию условием завершения и понимать лимит глубины в MySQL,
- замечать циклы и знать, какие защиты даёт каждый диалект.
Предполагается, что вы уверенно владеете обычными (нерекурсивными) CTE, соединениями и подзапросами из предыдущих курсов. Здесь мы лишь добавляем ссылку на самого себя.
1. Пример базы данных
Каждый запрос ниже выполняется над одной небольшой базой данных компании. Таблица employee ссылается сама на себя: каждая строка хранит id менеджера этого сотрудника, а у генерального директора это NULL. Один столбец manager_id кодирует всю оргструктуру произвольной глубины.
CREATE TABLE employee (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
manager_id INT NULL,
salary INT NOT NULL,
FOREIGN KEY (manager_id) REFERENCES employee(id)
);
INSERT INTO employee (id, name, manager_id, salary) VALUES
(1, 'Alice', NULL, 300000),
(2, 'Bob', 1, 200000),
(3, 'Carol', 1, 190000),
(4, 'Dan', 2, 120000),
(5, 'Eve', 2, 125000),
(6, 'Frank', 3, 110000),
(7, 'Grace', 4, 90000),
(8, 'Heidi', 4, 92000);Если нарисовать, линии подчинения выглядят так:
Alice (CEO)
- Bob
- Dan
- Grace
- Heidi
- Eve
- Carol
- FrankМы также используем вторую самоссылающуюся таблицу, category, для дерева каталога товаров. У неё та же форма: столбец parent_id, указывающий обратно в ту же таблицу.
CREATE TABLE category (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
parent_id INT NULL
);
INSERT INTO category (id, name, parent_id) VALUES
(1, 'Electronics', NULL),
(2, 'Computers', 1),
(3, 'Laptops', 2),
(4, 'Desktops', 2),
(5, 'Phones', 1),
(6, 'Audio', 1),
(7, 'Headphones', 6);