PRIOR в Oracle SQL или как правильно связать родителя и потомка

ORACLE

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

PRIOR — ключевое слово для иерархических запросов в Oracle SQL. Оно указывает, какая сторона связи относится к родителю, а какая — к потомку, в предложении CONNECT BY.
В паре с START WITH и псевдостолбцом LEVEL позволяет обходить деревья и графы: оргструктуры, категории, BOM, треды комментариев.

Где используют

  • Оргструктура: цепочки «начальник → подчинённый», выбор поддеревьев и предков.
  • Категории/меню: построение вложенных списков, хлебных крошек и путей.
  • BOM/спецификации: развёртка составов изделий по уровням.
  • Комментарии/диалоги: древовидные треды с сортировкой «соседей».

Синтаксис

SELECT ...
FROM t
  [NOCYCLE]
  CONNECT BY [PRIOR] child_expr = parent_expr
  [ AND ... ]
START WITH root_condition
[ ORDER SIBLINGS BY ... ]
  • PRIOR a = b означает «a из родителя равно b из потомка»; можно писать и наоборот: a = PRIOR b.
  • LEVEL — номер уровня (корень = 1).
  • CONNECT_BY_ROOT — значение корня, SYS_CONNECT_BY_PATH(col,'/') — путь.
  • NOCYCLE — обход с защитой от циклов; см. также CONNECT_BY_ISCYCLE.

100 примеров

1. Дерево сотрудников по менеджеру (устойчивый порядок соседей)

SELECT
  LPAD(' ', (LEVEL-1)*2) || emp_name AS emp_name,
  emp_id, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;

2. Список предков для конкретного сотрудника

SELECT
  LPAD(' ', (LEVEL-1)*2) || emp_name AS path_name,
  emp_id, manager_id, LEVEL
FROM employees
START WITH emp_id = :emp_id
CONNECT BY emp_id = PRIOR manager_id;

3. Путь от корня до узла (SYS_CONNECT_BY_PATH)

SELECT
  emp_id,
  SYS_CONNECT_BY_PATH(emp_name, ' / ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

4. Значение корня для каждого узла (CONNECT_BY_ROOT)

SELECT
  emp_id,
  CONNECT_BY_ROOT emp_name AS root_name,
  emp_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

5. Пометить листья в дереве сотрудников

SELECT
  emp_id, emp_name,
  CONNECT_BY_ISLEAF AS is_leaf
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

6. Глубина ограничена тремя уровнями

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL <= 3
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

7. Отсечение ветвей условием в CONNECT BY

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND status = 'ACTIVE';

8. Защита от циклов и их диагностика

SELECT emp_id, manager_id, CONNECT_BY_ISCYCLE AS is_cycle
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

9. Актуальная оргструктура по департаменту

SELECT dept_id, emp_id, emp_name, LEVEL
FROM employees
START WITH dept_id = :dept AND manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

10. Категории с путём «хлебных крошек»

SELECT
  cat_id,
  SYS_CONNECT_BY_PATH(cat_name, ' > ') AS breadcrumb
FROM categories
START WITH parent_id IS NULL
CONNECT BY PRIOR cat_id = parent_id;

11. Только листья в каталоге

SELECT cat_id, cat_name
FROM categories
WHERE CONNECT_BY_ISLEAF = 1
START WITH parent_id IS NULL
CONNECT BY PRIOR cat_id = parent_id;

12. Вывести только узлы второго уровня

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL = 2
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

13. Потомки заданного корня по идентификатору

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH emp_id = :root_id
CONNECT BY PRIOR emp_id = manager_id;

14. Все предки узла (подъём по цепочке)

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH emp_id = :leaf_id
CONNECT BY emp_id = PRIOR manager_id;

15. Пронумеровать узлы в порядке siblings

SELECT
  ROW_NUMBER() OVER (PARTITION BY CONNECT_BY_ROOT emp_id ORDER BY LEVEL) AS rn,
  emp_id, emp_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

16. Сумма зарплат в поддереве каждого корня

SELECT
  CONNECT_BY_ROOT emp_id AS root_id,
  SUM(salary) AS subtotal
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY CONNECT_BY_ROOT emp_id;

17. Количество потомков для каждого узла

SELECT e.emp_id,
       (SELECT COUNT(*)
        FROM employees
        START WITH emp_id = e.emp_id
        CONNECT BY PRIOR emp_id = manager_id) AS descendants
FROM employees e;

18. Развёртка BOM: изделие → компоненты

SELECT
  parent_part, child_part, qty, LEVEL
FROM bom
START WITH parent_part = :product
CONNECT BY PRIOR child_part = parent_part;

19. Сбор предков компонента (обратная цепочка)

SELECT parent_part, child_part, LEVEL
FROM bom
START WITH child_part = :component
CONNECT BY parent_part = PRIOR child_part;

20. Комментарии треда с сортировкой соседей по времени

SELECT
  LPAD(' ', (LEVEL-1)*2) || author || ': ' || text AS thread_line,
  created_at
FROM comments
START WITH parent_id IS NULL
CONNECT BY PRIOR comment_id = parent_id
ORDER SIBLINGS BY created_at;

Еще 20 примеров.

21. Путь идентификаторов через SYS_CONNECT_BY_PATH

SELECT
  comment_id,
  SYS_CONNECT_BY_PATH(comment_id, '/') AS id_path
FROM comments
START WITH parent_id IS NULL
CONNECT BY PRIOR comment_id = parent_id;

22. Выбор поддерева по начальнику

SELECT emp_id, emp_name
FROM employees
START WITH emp_id = :boss
CONNECT BY PRIOR emp_id = manager_id;

23. Выбор только корней (без родителей)

SELECT emp_id, emp_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

24. Ограничение глубины и сортировка соседей по имени

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL <= 4
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;

25. Исключить уволенных из иерархии

SELECT emp_id, emp_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND status <> 'FIRED';

26. Вывести только узлы‑листья (файл‑like)

SELECT path
FROM (
  SELECT SYS_CONNECT_BY_PATH(file_name, '/') AS path, CONNECT_BY_ISLEAF AS leaf
  FROM files
  START WITH parent_id IS NULL
  CONNECT BY PRIOR file_id = parent_id
)
WHERE leaf = 1;

27. Корень каждого узла (CONNECT_BY_ROOT)

SELECT emp_id, CONNECT_BY_ROOT emp_id AS root_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

28. Уровень как индикатор глубины

SELECT emp_id, emp_name, LEVEL AS depth
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

29. Нумерация в пределах каждого корня

SELECT
  CONNECT_BY_ROOT emp_id AS root_id,
  ROW_NUMBER() OVER (PARTITION BY CONNECT_BY_ROOT emp_id ORDER BY LEVEL) AS rn,
  emp_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

30. Ключ маршрута: корень/узел/глубина

SELECT
  CONNECT_BY_ROOT emp_id || ':' || emp_id || ':' || LEVEL AS key
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

31. Иерархия подразделений с фильтром по региону

SELECT dept_id, dept_name, LEVEL
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR dept_id = parent_id
  AND region = :region;

32. Только узлы на глубине 3 и глубже

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL >= 3
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

33. Счёт ветвей на каждом уровне

SELECT LEVEL, COUNT(*) AS cnt
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY LEVEL
ORDER BY LEVEL;

34. Ветвление с несколькими корнями (несколько START WITH)

SELECT emp_id, emp_name
FROM employees
START WITH dept_id IN (10, 20) AND manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

35. Выделить только подчинённых без руководителей

SELECT emp_id, emp_name
FROM employees
START WITH manager_id IS NOT NULL
CONNECT BY PRIOR emp_id = manager_id;

36. Поиск путей до конкретного листа

SELECT SYS_CONNECT_BY_PATH(emp_name, ' > ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND emp_id <> :target;

37. Счёт потомков через подзапрос на LEVEL > 1

SELECT e.emp_id,
       (SELECT COUNT(*) FROM employees
         START WITH emp_id = e.emp_id
         CONNECT BY PRIOR emp_id = manager_id AND LEVEL > 1) AS child_cnt
FROM employees e;

38. Обрезка ветвей при достижении лимита глубины

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND LEVEL <= :max_depth;

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

SELECT SYS_CONNECT_BY_PATH(emp_name, ' / ') AS ancestor_path
FROM employees
START WITH emp_id = :leaf
CONNECT BY emp_id = PRIOR manager_id;

40. Идентификатор корня и количество листьев

SELECT
  CONNECT_BY_ROOT emp_id AS root_id,
  SUM(CASE WHEN CONNECT_BY_ISLEAF = 1 THEN 1 ELSE 0 END) AS leaf_cnt
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY CONNECT_BY_ROOT emp_id;

Еще 20 примеров.

41. Сортировка ветвей и глобальная сортировка итогов

SELECT*
FROM (
  SELECT emp_id, emp_name, LEVEL
  FROM employees
  START WITH manager_id IS NULL
  CONNECT BY PRIOR emp_id = manager_id
  ORDER SIBLINGS BY emp_name
)
ORDER BY LEVEL, emp_name;

42. Дерево сотрудников по менеджеру (устойчивый порядок соседей)

SELECT
  LPAD(' ', (LEVEL-1)*2) || emp_name AS emp_name,
  emp_id, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;

43. Список предков для конкретного сотрудника

SELECT
  LPAD(' ', (LEVEL-1)*2) || emp_name AS path_name,
  emp_id, manager_id, LEVEL
FROM employees
START WITH emp_id = :emp_id
CONNECT BY emp_id = PRIOR manager_id;

44. Путь от корня до узла (SYS_CONNECT_BY_PATH)

SELECT
  emp_id,
  SYS_CONNECT_BY_PATH(emp_name, ' / ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

45. Значение корня для каждого узла (CONNECT_BY_ROOT)

SELECT
  emp_id,
  CONNECT_BY_ROOT emp_name AS root_name,
  emp_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

46. Пометить листья в дереве сотрудников

SELECT
  emp_id, emp_name,
  CONNECT_BY_ISLEAF AS is_leaf
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

47. Глубина ограничена тремя уровнями

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL <= 3
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

48. Отсечение ветвей условием в CONNECT BY

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND status = 'ACTIVE';

49. Защита от циклов и их диагностика

SELECT emp_id, manager_id, CONNECT_BY_ISCYCLE AS is_cycle
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

50. Актуальная оргструктура по департаменту

SELECT dept_id, emp_id, emp_name, LEVEL
FROM employees
START WITH dept_id = :dept AND manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

51. Категории с путём «хлебных крошек»

SELECT
  cat_id,
  SYS_CONNECT_BY_PATH(cat_name, ' > ') AS breadcrumb
FROM categories
START WITH parent_id IS NULL
CONNECT BY PRIOR cat_id = parent_id;

52. Только листья в каталоге

SELECT cat_id, cat_name
FROM categories
WHERE CONNECT_BY_ISLEAF = 1
START WITH parent_id IS NULL
CONNECT BY PRIOR cat_id = parent_id;

53. Вывести только узлы второго уровня

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL = 2
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

54. Потомки заданного корня по идентификатору

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH emp_id = :root_id
CONNECT BY PRIOR emp_id = manager_id;

55. Все предки узла (подъём по цепочке)

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH emp_id = :leaf_id
CONNECT BY emp_id = PRIOR manager_id;

56. Пронумеровать узлы в порядке siblings

SELECT
  ROW_NUMBER() OVER (PARTITION BY CONNECT_BY_ROOT emp_id ORDER BY LEVEL) AS rn,
  emp_id, emp_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

57. Сумма зарплат в поддереве каждого корня

SELECT
  CONNECT_BY_ROOT emp_id AS root_id,
  SUM(salary) AS subtotal
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY CONNECT_BY_ROOT emp_id;

58. Количество потомков для каждого узла

SELECT e.emp_id,
       (SELECT COUNT(*)
        FROM employees
        START WITH emp_id = e.emp_id
        CONNECT BY PRIOR emp_id = manager_id) AS descendants
FROM employees e;

59. Развёртка BOM: изделие → компоненты

SELECT
  parent_part, child_part, qty, LEVEL
FROM bom
START WITH parent_part = :product
CONNECT BY PRIOR child_part = parent_part;

60. Сбор предков компонента (обратная цепочка)

SELECT parent_part, child_part, LEVEL
FROM bom
START WITH child_part = :component
CONNECT BY parent_part = PRIOR child_part;

Еще 20 примеров.

61. Комментарии треда с сортировкой соседей по времени

SELECT
  LPAD(' ', (LEVEL-1)*2) || author || ': ' || text AS thread_line,
  created_at
FROM comments
START WITH parent_id IS NULL
CONNECT BY PRIOR comment_id = parent_id
ORDER SIBLINGS BY created_at;

62. Путь идентификаторов через SYS_CONNECT_BY_PATH

SELECT
  comment_id,
  SYS_CONNECT_BY_PATH(comment_id, '/') AS id_path
FROM comments
START WITH parent_id IS NULL
CONNECT BY PRIOR comment_id = parent_id;

63. Выбор поддерева по начальнику

SELECT emp_id, emp_name
FROM employees
START WITH emp_id = :boss
CONNECT BY PRIOR emp_id = manager_id;

64. Выбор только корней (без родителей)

SELECT emp_id, emp_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

65. Ограничение глубины и сортировка соседей по имени

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL <= 4
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;

66. Исключить уволенных из иерархии

SELECT emp_id, emp_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND status <> 'FIRED';

67. Вывести только узлы‑листья (файл‑like)

SELECT path
FROM (
  SELECT SYS_CONNECT_BY_PATH(file_name, '/') AS path, CONNECT_BY_ISLEAF AS leaf
  FROM files
  START WITH parent_id IS NULL
  CONNECT BY PRIOR file_id = parent_id
)
WHERE leaf = 1;

68. Корень каждого узла (CONNECT_BY_ROOT)

SELECT emp_id, CONNECT_BY_ROOT emp_id AS root_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

69. Уровень как индикатор глубины

SELECT emp_id, emp_name, LEVEL AS depth
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

70. Нумерация в пределах каждого корня

SELECT
  CONNECT_BY_ROOT emp_id AS root_id,
  ROW_NUMBER() OVER (PARTITION BY CONNECT_BY_ROOT emp_id ORDER BY LEVEL) AS rn,
  emp_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

71. Ключ маршрута: корень/узел/глубина

SELECT
  CONNECT_BY_ROOT emp_id || ':' || emp_id || ':' || LEVEL AS key
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

72. Иерархия подразделений с фильтром по региону

SELECT dept_id, dept_name, LEVEL
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR dept_id = parent_id
  AND region = :region;

73. Только узлы на глубине 3 и глубже

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL >= 3
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

74. Счёт ветвей на каждом уровне

SELECT LEVEL, COUNT(*) AS cnt
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY LEVEL
ORDER BY LEVEL;

75. Ветвление с несколькими корнями (несколько START WITH)

SELECT emp_id, emp_name
FROM employees
START WITH dept_id IN (10, 20) AND manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

76. Выделить только подчинённых без руководителей

SELECT emp_id, emp_name
FROM employees
START WITH manager_id IS NOT NULL
CONNECT BY PRIOR emp_id = manager_id;

77. Поиск путей до конкретного листа

SELECT SYS_CONNECT_BY_PATH(emp_name, ' > ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND emp_id <> :target;

78. Счёт потомков через подзапрос на LEVEL > 1

SELECT e.emp_id,
       (SELECT COUNT(*) FROM employees
         START WITH emp_id = e.emp_id
         CONNECT BY PRIOR emp_id = manager_id AND LEVEL > 1) AS child_cnt
FROM employees e;

79. Обрезка ветвей при достижении лимита глубины

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND LEVEL <= :max_depth;

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

SELECT SYS_CONNECT_BY_PATH(emp_name, ' / ') AS ancestor_path
FROM employees
START WITH emp_id = :leaf
CONNECT BY emp_id = PRIOR manager_id;

Еще 20 примеров.

81. Идентификатор корня и количество листьев

SELECT
  CONNECT_BY_ROOT emp_id AS root_id,
  SUM(CASE WHEN CONNECT_BY_ISLEAF = 1 THEN 1 ELSE 0 END) AS leaf_cnt
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY CONNECT_BY_ROOT emp_id;

82. Сортировка ветвей и глобальная сортировка итогов

SELECT*
FROM (
  SELECT emp_id, emp_name, LEVEL
  FROM employees
  START WITH manager_id IS NULL
  CONNECT BY PRIOR emp_id = manager_id
  ORDER SIBLINGS BY emp_name
)
ORDER BY LEVEL, emp_name;

83. Дерево сотрудников по менеджеру (устойчивый порядок соседей)

SELECT
  LPAD(' ', (LEVEL-1)*2) || emp_name AS emp_name,
  emp_id, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;

84. Список предков для конкретного сотрудника

SELECT
  LPAD(' ', (LEVEL-1)*2) || emp_name AS path_name,
  emp_id, manager_id, LEVEL
FROM employees
START WITH emp_id = :emp_id
CONNECT BY emp_id = PRIOR manager_id;

85. Путь от корня до узла (SYS_CONNECT_BY_PATH)

SELECT
  emp_id,
  SYS_CONNECT_BY_PATH(emp_name, ' / ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

86. Значение корня для каждого узла (CONNECT_BY_ROOT)

SELECT
  emp_id,
  CONNECT_BY_ROOT emp_name AS root_name,
  emp_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

87. Пометить листья в дереве сотрудников

SELECT
  emp_id, emp_name,
  CONNECT_BY_ISLEAF AS is_leaf
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

88. Глубина ограничена тремя уровнями

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL <= 3
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

89. Отсечение ветвей условием в CONNECT BY

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND status = 'ACTIVE';

90. Защита от циклов и их диагностика

SELECT emp_id, manager_id, CONNECT_BY_ISCYCLE AS is_cycle
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

91. Актуальная оргструктура по департаменту

SELECT dept_id, emp_id, emp_name, LEVEL
FROM employees
START WITH dept_id = :dept AND manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

92. Категории с путём «хлебных крошек»

SELECT
  cat_id,
  SYS_CONNECT_BY_PATH(cat_name, ' > ') AS breadcrumb
FROM categories
START WITH parent_id IS NULL
CONNECT BY PRIOR cat_id = parent_id;

93. Только листья в каталоге

SELECT cat_id, cat_name
FROM categories
WHERE CONNECT_BY_ISLEAF = 1
START WITH parent_id IS NULL
CONNECT BY PRIOR cat_id = parent_id;

94. Вывести только узлы второго уровня

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL = 2
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

95. Потомки заданного корня по идентификатору

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH emp_id = :root_id
CONNECT BY PRIOR emp_id = manager_id;

96. Все предки узла (подъём по цепочке)

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH emp_id = :leaf_id
CONNECT BY emp_id = PRIOR manager_id;

97. Пронумеровать узлы в порядке siblings

SELECT
  ROW_NUMBER() OVER (PARTITION BY CONNECT_BY_ROOT emp_id ORDER BY LEVEL) AS rn,
  emp_id, emp_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

98. Сумма зарплат в поддереве каждого корня

SELECT
  CONNECT_BY_ROOT emp_id AS root_id,
  SUM(salary) AS subtotal
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY CONNECT_BY_ROOT emp_id;

99. Количество потомков для каждого узла

SELECT e.emp_id,
       (SELECT COUNT(*)
        FROM employees
        START WITH emp_id = e.emp_id
        CONNECT BY PRIOR emp_id = manager_id) AS descendants
FROM employees e;

100. Развёртка BOM: изделие → компоненты

SELECT
  parent_part, child_part, qty, LEVEL
FROM bom
START WITH parent_part = :product
CONNECT BY PRIOR child_part = parent_part;

Частые ошибки и подводные камни

  1. Направление связи. Перепутали стороны в условии — дерево «разворачивается» вверх/вниз неверно. Проверяйте с маленьким примером.
  2. Фильтры в WHERE. Условия в WHERE применяются после построения иерархии — ими легко «обрубить» ветви. Для отсечения веток используйте дополнительные условия прямо в CONNECT BY.
  3. Сортировка соседей. Обычное ORDER BY не сохраняет группировку ветвей; используйте ORDER SIBLINGS BY.
  4. Циклы. При потенциальных кольцах добавляйте NOCYCLE и колонку CONNECT_BY_ISCYCLE.
  5. Производительность. Глубокие деревья + широкие ветви → экспоненциальный рост. Ограничивайте LEVEL и добавляйте селективные условия в CONNECT BY.

Альтернативы

  • Рекурсивный WITH (subquery factoring) на базе UNION ALL — гибче для сложных правил и циклов.
  • Материализованные пути (хранить путь или список предков) — быстро для «read‑heavy», дороже на запись.

Заключение

PRIOR вместе с CONNECT BY остаётся быстрым способом обходить иерархии прямо в SQL. Контролируйте направление связи, применяйте ORDER SIBLINGS BY для аккуратной выдачи и используйте средства против циклов.

Документация

Oracle SQL Language Reference — Hierarchical Queries

LEVEL, CONNECT_BY_ROOT, CONNECT_BY_ISLEAF, SYS_CONNECT_BY_PATH

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

CONNECT BY в Oracle SQL: как строить иерархии, древовидные структуры и связи

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

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

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