В качестве альтернативы можно использовать представления и объединять (UNION) результаты. Если созданы следующие представления:

create view v1 as

select ename,mgr,empno

from emp where ename = JONES

create view v2 as

select ename,mgr,empno from emp

where mgr = (select empno from v1)

create view v3 as

select ename,mgr,empno from emp

where mgr in (select empno from v2)

решение будет таким:

select ename from v1 union

select ename from v2 union

select ename from v3

Обсуждение

DB2 и SQL Server

Рекурсивный оператор WITH упрощает решение этой задачи. Первая часть оператора WITH, верхняя часть UNION ALL возвращает строку служащего JONES. Необходимо получить ENAME, чтобы увидеть имя, и EMPNO, чтобы использовать его для объединения. Нижняя часть UNION ALL рекурсивно объединяет EMP.MGR с X.EMPNO. Ус-

ловие объединения будет применяться до тех пор, пока не будет исчерпано результирующее множество.

Oracle

Оператор START WITH указывает запросу сделать JONES корневым узлом. Условие в операторе CONNECT BY управляет обходом дерева, который будет продолжаться до тех пор, пока условие истинно.

PostgreSQL и MySQL

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

Определение узлов: ветвления, концевого, корневого

Задача

Требуется определить, узлом какого типа является данная строка: концевым узлом, узлом ветвления или корневым узлом. В данном примере концевым узлом считается служащий, не являющийся руководителем. Узел ветвления - это служащий, являющийся как руководителем, так и подчиненным. Корневой узел - служащий, не имеющий руководителя. Для отражения статуса каждой строки в иерархии используется 1 (TRUE) или 0 (FALSE). Должно быть получено следующее результирующее множество:

ENAME IS LEAF IS BRANCH IS ROOT

KING 0 0 1

JONES 0 1 0

SCOTT 0 1 0

FORD 0 1 0

CLARK 0 1 0

BLAKE 0 1 0

ADAMS 1 0 0

MILLER 1 0 0

JAMES 1 0 0

TURNER 1 0 0

ALLEN 1 0 0

WARD 1 0 0

MARTIN 1 0 0

SMITH 1 0 0

Решение

Важно понимать, что таблица EMP смоделирована как древовидная иерархия, не рекурсивная, значение поля MGR корневых узлов - NULL. Если бы в EMP использовалась рекурсивная структура данных, корне-

вые узлы были бы автореферентными (т. е. ссылались бы сами на себя, и значением поля MGR для служащего KING было бы значение поля EMPNO этого же самого KING). Автореферентность показалась мне лишенной здравого смысла, и поэтому в поле MGR корневых узлов я поместил значение NULL. Значительно упростят работу с древовидными иерархиями и сделают их потенциально более эффективными, чем рекурсивные иерархии, операторы CONNECT BY для пользователей Oracle и WITH для пользователей DB2/SQL Server. Применяя CONNECT

BY или WITH при работе с рекурсивной иерархией, будьте начеку: все может закончиться циклом. Чтобы этого не произошло, в коде должны быть предприняты соответствующие меры предосторожности.

DB2, PostgreSQL, MySQL и SQL Server

Чтобы верно определить один из трех типов узлов и возвратить соответствующее логическое значение (1 или 0), используйте три скалярных подзапроса:

1 select e.ename,

2 (select sign(count(*)) from emp d

3 where 0 =

4 (select count(*) from emp f

5 where f.mgr = e.empno)) as is leaf, b (select sign(count(*)) from emp d

7 where d.mgr = e.empno

8 and e.mgr is not null) as is branch,

9 (select sign(count(*)) from emp d

10 where d.empno = e.empno

11 and d.mgr is null) as is root

12 from emp e

13 order by 4 desc,3 desc

Oracle

Решение с применением скалярных подзапросов подойдет и для Oracle и должно использоваться для версий до Oracle Database lOg. Следующее решение для идентификации корневых и концевых узлов опирается на предлагаемые Oracle встроенные функции (которые были введены в Oracle Database lOg). Это функции CONNECT BY ROOT и CON-NECT BY ISLEAF соответственно:

I select ename,

2 connect by isleaf is leaf,

3 (select count(*) from emp e

4 where e.mgr = emp.empno

5 and emp.mgr is not null

b and rownum = 1) is branch,

7 decode(ename,connect by root(ename),1,0) is root

8 from emp

9 start with mgr is null

10 connect by prior empno = mgr

II order by 4 desc, 3 desc