CONNECT_BY_ROOT в Oracle SQL: как определить корень иерархии для любой строки

ORACLE

🟢 CONNECT_BY_ROOT в Oracle SQL. Введение

Когда вы строите иерархию с помощью CONNECT BY, может возникнуть вопрос:

❓ А с какого элемента вообще начинается путь для данной строки?

Оператор CONNECT_BY_ROOT позволяет на это ответить. Он возвращает значение столбца от начального (корневого) узла, указанного в START WITH, для каждой строки результата.

🔤 Написание

sql
SELECT CONNECT_BY_ROOT столбец AS корень
FROM таблица
START WITH ...
CONNECT BY PRIOR ...;

🔄 Где часто используется

  • Чтобы узнать главного начальника для любого сотрудника

  • Определить основную категорию для подкатегории

  • Найти верхний проект для вложенной задачи

  • Для группировки по «верхнему уровню»

  • Построение путей: от корня к листу

🧪 10 Примеров использования CONNECT_BY_ROOT с пояснениями

1️⃣ Показать корневого менеджера для каждого сотрудника

sql
SELECT CONNECT_BY_ROOT first_name AS top_manager,
first_name AS employee
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Каждому сотруднику сопоставляем его главного начальника.

2️⃣ Показать путь задачи от главной задачи до подзадачи

sql
SELECT CONNECT_BY_ROOT task_name AS root_task,
task_name AS sub_task
FROM tasks
START WITH parent_task_id IS NULL
CONNECT BY PRIOR task_id = parent_task_id;

Вложенные задачи с их «верхней» задачей.

3️⃣ Иерархия товаров и компонентов

sql
SELECT CONNECT_BY_ROOT product_id AS main_product,
component_id
FROM product_components
START WITH product_id = 100
CONNECT BY PRIOR component_id = product_id;

Все детали и компоненты от основного продукта.

4️⃣ Путь вверх от сотрудника

sql
SELECT CONNECT_BY_ROOT employee_id AS root_id,
employee_id
FROM employees
START WITH employee_id = 123
CONNECT BY PRIOR manager_id = employee_id;

Кто вверху над конкретным сотрудником?

5️⃣ Показать только конечные элементы с корнем

sql
SELECT CONNECT_BY_ROOT first_name AS top_manager,
first_name AS leaf
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
AND CONNECT_BY_ISLEAF = 1;

Сопоставление начальника и каждого конечного подчинённого.

6️⃣ Использование CONNECT_BY_ROOT в GROUP BY

sql
SELECT CONNECT_BY_ROOT department_id AS top_dept,
COUNT(*) AS total_employees
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR department_id = parent_id
GROUP BY CONNECT_BY_ROOT department_id;

Считаем сотрудников по головным отделам.

7️⃣ Формирование пути вверх в SELECT

sql
SELECT LEVEL, CONNECT_BY_ROOT first_name || ' → ' || first_name AS path
FROM employees
START WITH employee_id = 125
CONNECT BY PRIOR manager_id = employee_id;

Получаем путь от корня до текущего узла.

8️⃣ CONNECT_BY_ROOT + CASE

sql
SELECT first_name,
CASE CONNECT_BY_ROOT job_id
WHEN 'AD_PRES' THEN 'Executive'
ELSE 'Staff'
END AS role_type
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Классифицируем по начальнику.

9️⃣ Подсчёт «ветвей» дерева по корню

sql
SELECT CONNECT_BY_ROOT employee_id AS root_id,
COUNT(*) AS team_size
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
GROUP BY CONNECT_BY_ROOT employee_id;

Сколько человек в каждой ветви под каждым топ-менеджером.

🔟 CONNECT_BY_ROOT в представлении

sql
CREATE OR REPLACE VIEW employee_paths AS
SELECT employee_id,
CONNECT_BY_ROOT employee_id AS root_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Каждому сотруднику сопоставляем главу его ветки.

🧩 Заключение

CONNECT_BY_ROOT — один из самых полезных псевдостолбцов в иерархических запросах.
Он позволяет для каждой строки узнать, откуда она пошла — кто был начальной точкой в дереве.

💡 Практически всегда используется в комбинации с:

  • LEVEL

  • CONNECT_BY_ISLEAF

  • PRIOR

  • START WITH ... CONNECT BY ...

🔜 Следующая статья:

Оператор DISTINCT в Oracle SQL — как убрать дубликаты из результата

0
Понравилась статья? Поделиться с друзьями:
Вам также может быть интересно

SELECT в Oracle SQL — 100 простых примеров для старта

SELECT в Oracle SQL. Введение SELECT — основной оператор выборки в Oracle SQL. Он строит результирующие наборы из таблиц и представлений, поддерживает фильтрацию, сортировку, группировку, подзапросы, соединения, аналитические…

0 0 голоса
Рейтинг статьи
Подписаться
Уведомить о
guest
0 комментариев
Старые
Новые Популярные
Межтекстовые Отзывы
Посмотреть все комментарии