select rtrim(a.ename - b.ename - c.ename - d.ename, - )

from emp a

join

emp b on (a.empno=b.mgr)

join

emp c on (b.empno=c.mgr)

left join

emp d on (c.empno=d.mgr) where a.ename = KING

PARTIAL TREE

KING KING KING KING KING KING KING KING KING KING KING KING KING

BLAKE BLAKE BLAKE BLAKE BLAKE BLAKE CLARK CLARK JONES JONES JONES JONES JONES

ALLEN

JAMES

MARTIN

TURNER

WARD

MILLER

FORD

FORD - SMITH

SCOTT

SCOTT - ADAMS

Заключительный шаг - с помощью операции UNION вставить строку служащего KING в верхушку PARTIAL TREE и получить требуемое результирующее множество.

Выбор всех дочерних строк для заданной строки Задача

Требуется найти всех служащих, которые прямо или косвенно (т. е. являются подчиненными того, кто подчиняется JONES) подчиняются JONES. Список подчиненных JONES показан ниже (JONES включен в результирующее множество):

ENAME

JONES

SCOTT

ADAMS

FORD

SMITH

Решение

Здесь очень пригодится возможность переходить в самый верх или самый низ дерева иерархии. Для этого решения не требуется специаль-

Используйте оператор CONNECT BY и задайте START WITH ENAME = JONES, чтобы найти всех подчиненных JONES:

1 select ename

2 from emp

3 start with ename = JONES

4 connect by prior empno = mgr

PostgreSQL и MySQL

Необходимо заранее знать количество узлов в дереве. Следующий запрос показывает, как определить глубину иерархии:

/* находим EMPNO служащего JONES */ select ename,empno,mgr from emp where ename = JONES

ENAME EMPNO MGR

JONES 75bb 7839

/* есть ли служащие, находящиеся в прямом подчинении у JONES? */ select count(*) from emp where mgr = 7566

COUNT(*)

ного форматирования. Цель - просто выбрать всех служащих, работающих под руководством JONES, включая самого JONES. Подобные задачи, на самом деле, демонстрируют ценность таких рекурсивных расширений SQL, как операторы CONNECT BY для Oracle и WITH для SQL Server/DB2.

DB2 и SQL Server

С помощью рекурсивного оператора WITH найдите всех подчиненных JONES. Начинайте с JONES, задав WHERE ENAME = JONES в первом из двух объединяемых запросов.

/* у JONES двое подчиненных, найдем их EMPNO */ select ename,empno,mgr from emp where mgr = 7566

ENAME EMPNO MGR

SCOTT 7788 7566

FORD 7902 7566

/* есть ли подчиненные у SCOTT или FORD? */ select count(*) from emp where mgr in (7788,7902)

COUNT(.)

/* у SCOTT и FORD двое подчиненных, находим их EMPNO */ select ename,empno,mgr

from emp

where mgr in (7788,7902)

ENAME EMPNO MGR

SMITH 7369 7902

ADAMS 7876 7788

/* есть ли подчиненные у SMITH или ADAMS? */ select count(*) from emp

where mgr in (7369,7876)

COUNT(.)

Иерархия, начинающаяся со служащего JONES, заканчивается служащими SMITH и ADAMS, таким образом, имеем трехуровневую иерархию. Теперь, когда известна глубина, можно приступать к обходу иерархии сверху вниз.

Сначала дважды выполним рефлексивное объединение таблицы EMP. Затем произведем обратное разворачивание вложенного представления Х, чтобы преобразовать три столбца и две строки в один столбец и шесть строк (в PostgreSQL как альтернативу запросу к сводной таблице T100 можно использовать GENERATE SERIES(1,6):

1 select distinct

2 case t100.id

3 when 1 then root