объединение таблицы EMP. В этом решении используется оператор конкатенации PostgreSQL, двойная вертикальная черта ():

1 select a.ename-->b.ename

2 -->c.ename as leaf branch root

3 from emp a, emp b, emp c

4 where a.ename = MILLER

5 and a.mgr = b.empno

6 and b.mgr = c.empno

Для MySQL просто используйте функцию CONCAT; это решение будет работать и для PostgreSQL.

Обсуждение

DB2 и SQL Server

Здесь обход дерева иерархии начинается с концевого узла и выполняется вверх до корневого узла (для тренировки попробуйте обойти дерево в обратном направлении). Верхняя часть оператора UNION ALL просто ищет строку служащего MILLER (концевой узел). В нижней части UNION ALL идет поиск служащего, который является руководителем MILLER, затем руководителя этого служащего и т. д. Процесс поиска руководителя руководителя продолжается до тех пор, пока не будет найден руководитель самого высокого уровня (корневой узел). В рекурсивных запросах DB2 использует поле DEPTH, представляющее уровень иерархии. DEPTH начинается со значения 0 и затем автоматически увеличивается на 1 при каждом выявлении следующего руководителя.

Интересный и всеобъемлющий рассказ об операторе WITH с упором на его рекурсивное использование можно найти в статье Джонатана Генника Understanding the WITH Clause по адресу http:/ /gennick.com/with.htm.

Далее второй запрос UNION ALL объединяет рекурсивное представление Х с таблицей EMP для определения отношений родитель-потомок. На данный момент имеем следующий запрос, использующий оператор конкатенации SQL Server:

with x (tree, mgr, depth) as (

select cast(ename as varchar(100)), mgr, 0 from emp where ename = MILLER union all

select cast(e.ename as varchar(100)), e.mgr, x.depth+1 from emp e, x where x.mgr = e.empno

select tree leaf branch root, depth from x

TREE DEPTH

MILLER 0

CLARK 1

KING 2

Итак, основная часть задачи выполнена: получена вся иерархия взаимоотношений снизу вверх, начиная с MILLER. Осталось только отформатировать. Поскольку обход дерева выполняется рекурсивно, просто соединяем текущее значение ENAME из EMP с предыдущим и получаем такое результирующее множество:

with x (tree,mgr,depth)

as (

select cast(ename as varchar(100)), mgr, 0

from emp

where ename = MILLER

union all

select cast(x.tree+-->+e.ename as varchar(100)), e.mgr, x.depth+1

from emp e, x

where x.mgr = e.empno

select depth, tree

from x

DEPTH TREE

0 MILLER

1 MILLER-->CLARK

2 MILLER-->CLARK-->KING

Заключительный шаг - выбрать только последнюю строку иерархии. Это можно сделать по-разному, но в решении для определения корневого узла используется значение DEPTH (очевидно, что если бы руководителем CLARK был не KING, фильтр по DEPTH пришлось бы изменить; более универсальное решение, не требующее применения такого фильтра, представлено в следующем рецепте).

Oracle

В решении для Oracle всю работу выполняет оператор CONNECT BY. Начиная с MILLER, проходим весь путь до KING без всяких объединений. Выражение в операторе CONNECT BY определяет отношения между данными и то, как будет выполняться обход дерева:

select ename from emp

MILLER

CLARK

KING

Ключевое слово PRIOR обеспечивает возможность доступа к значениям предыдущей записи иерархии. Таким образом, для любого данного EMPNO с помощью PRIOR MGR можно обратиться к номеру руководителя этого служащего. Конструкцию CONNECT BY PRIOR MGR = EMPNO можно рассматривать как представляющую объединение между, в данном случае, родителем и потомком.

Более подробную информацию об операторе CONNECT BY и связанных с ним возможностях можно найти в статьях Oracle Technology Network Querying Hierarchies: Top-of-the-Line Support no адресу http: www.oracle.com/technology/oramag/webcolumns/ 2003/techarticles/gennick connectby.html и New CONNECT BY Features in Oracle Database 10g по адресу http: www.oracle.com/ technology/oramag/webcolumns/2003/techarticles/gennick con-nectby 10g.html.

На данный момент мы успешно отобразили всю иерархию, начиная с MILLER и заканчивая KING. Задача по большей части решена. Осталось только отформатировать. С помощью функции SYS CONNECT BY PATH добавьте каждое значение ENAME в конец предшествующего ему значению:

select sys connect by path(ename,-->) tree from emp

start with ename = MILLER connect by prior mgr = empno

TREE

-->MILLER

-->MILLER-->CLARK

-->MILLER-->CLARK-->KING

Поскольку нам нужна только полная иерархия, фильтруем результаты по псевдостолбцу LEVEL (более универсальный подход показан в следующем рецепте):

select sys connect by path(ename,-->) tree from emp where level = 3 start with ename = MILLER connect by prior mgr = empno

TREE

start with ename = MILLER connect by prior mgr = empno

ENAME