← Каталог
Общие табличные выражения (CTE) — Рекурсивные общие табличные выражения
Фрагмент из «Общие табличные выражения (CTE)»: Рекурсивные общие табличные выражения.
WITH RECURSIVE иерархия_отделов AS (
-- Начальный запрос: корневые элементы
SELECT
department_id,
department_name,
parent_department_id,
department_name AS full_path,
1 AS level
FROM departments
WHERE parent_department_id IS NULL
UNION ALL
-- Рекурсивный запрос: дочерние элементы
SELECT
d.department_id,
d.department_name,
d.parent_department_id,
CONCAT(h.full_path, ' -> ', d.department_name) AS full_path,
h.level + 1 AS level
FROM departments d
INNER JOIN иерархия_отделов h ON d.parent_department_id = h.department_id
)
SELECT
department_id,
department_name,
full_path,
level
FROM иерархия_отделов
ORDER BY full_path;WITH RECURSIVE иерархия_отделов AS (
-- Начальный запрос: корневые элементы
SELECT
department_id,
department_name,
parent_department_id,
department_name AS full_path,
1 AS level
FROM departments
WHERE parent_department_id IS NULL
UNION ALL
-- Рекурсивный запрос: дочерние элементы
SELECT
d.department_id,
d.department_name,
d.parent_department_id,
CONCAT(h.full_path, ' -> ', d.department_name) AS full_path,
h.level + 1 AS level
FROM departments d
INNER JOIN иерархия_отделов h ON d.parent_department_id = h.department_id
)
SELECT
department_id,
department_name,
full_path,
level
FROM иерархия_отделов
ORDER BY full_path;WITH RECURSIVE категори_дерево AS (
SELECT
category_id,
category_name,
parent_category_id,
category_name AS path,
0 AS depth
FROM categories
WHERE parent_category_id IS NULL
UNION ALL
SELECT
c.category_id,
c.category_name,
c.parent_category_id,
CONCAT(ct.path, ' > ', c.category_name) AS path,
ct.depth + 1 AS depth
FROM categories c
INNER JOIN категори_дерево ct ON c.parent_category_id = ct.category_id
)
SELECT
category_id,
REPEAT(' ', depth) || category_name AS отступ_категория,
path,
depth
FROM категори_дерево
ORDER BY path;WITH RECURSIVE категори_дерево AS (
SELECT
category_id,
category_name,
parent_category_id,
category_name AS path,
0 AS depth
FROM categories
WHERE parent_category_id IS NULL
UNION ALL
SELECT
c.category_id,
c.category_name,
c.parent_category_id,
CONCAT(ct.path, ' > ', c.category_name) AS path,
ct.depth + 1 AS depth
FROM categories c
INNER JOIN категори_дерево ct ON c.parent_category_id = ct.category_id
)
SELECT
category_id,
REPEAT(' ', depth) || category_name AS отступ_категория,
path,
depth
FROM категори_дерево
ORDER BY path;