11 12 13 14 15 16 17 18 19 20 21

23 24 25

26 27 28 29 30 31

33 34

where union

select

from

where union select

from

where union select

from

where where

mgr is null

concat(a.ename, - ,b.ename)

emp a

join

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

concat(a.ename, - ,

b.ename, - ,c.ename)

emp a join

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

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

concat(a.ename, - ,b.ename, c.ename, - ,d.ename)

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) a.ename = KING ) x

tree is not null

by 1

Обсуждение

DB2 и SQL Server

Первый шаг - определить корневую строку (служащий KING) в рекурсивном представлении Х верхней части оператора UNION ALL. Следующий шаг - найти подчиненных KING и их подчиненных, если таковые имеются, через объединение рекурсивного представления Х с таблицей EMP. Повторения выполняются до тех пор, пока не будут возвращены все служащие. Результирующее множество, возвращаемое рекурсивным представлением Х и отличающееся от окончательного результата только отсутствием форматирования, показано ниже:

with x (ename,empno)

as (

select cast(ename as varchar(100)),empno

from emp

where mgr is null

union all

select cast(e.ename as varchar(100)),e.empno

from emp e, x

KING

JONES

SCOTT

ADAMS

FORD

SMITH

BLAKE

ALLEN

WARD

MARTIN

TURNER

JAMES CLARK MILLER

Получены все строки иерархии (которые могут быть полезны), но без форматирования мы не можем сказать, кто из сотрудников является руководителями. Объединяя строку каждого сотрудника со строкой его руководителя, получаем более выразительный результат. Для этого просто используем

cast(x.ename+,+e.ename as varchar(100))

в операторе SELECT нижней части UNION ALL в рекурсивном представлении Х.

Оператор WITH исключительно полезен при решении такого типа задач, потому что иерархия может меняться (например, концевые узлы могут стать узлами ветвления), но при этом не придется менять запрос.

Oracle

Оператор CONNECT BY возвращает строки иерархической последовательности. Оператор START WITH определяет корневую строку. Если выполнить решение без SYS CONNECT BY PATH, мы получим необходимые строки (что может быть полезным), но без форматирования, отображающего существующие взаимотношения:

select ename emp tree from emp

start with mgr is null connect by prior empno = mgr

EMP TREE

KING JONES

where e.mgr = x.empno

select ename emp tree from x

EMP TREE

..KING

....JONES

......SCOTT

........ADAMS

......FORD

........SMITH

....BLAKE

......ALLEN

......WARD

......MARTIN

......TURNER

......JAMES

....CLARK

......MILLER

По отступам в данном выводе можно понять, кто является руководителем, а кто - подчиненным. Например, KING не подчиняется никому. JONES подчиняется KING. SCOTT подчиняется JONES. ADAMS подчиняется SCOTT.

Если посмотреть на соответствующие строки решения, использующего SYS CONNECT BY PATH, можно увидеть, что SYS CONNECT

BY PATH накапливает иерархию. Получая новый узел, мы видим ивсе предыдущие узлы:

KING

KING - JONES

KING - JONES - SCOTT

KING - JONES - SCOTT - ADAMS

SCOTT ADAMS

FORD

SMITH

BLAKE

ALLEN

WARD

MARTIN

TURNER JAMES

CLARK MILLER

Применяя псевдостолбец LEVEL и функцию LPAD, можно более ясно увидеть иерархию и в конечном счете понять, почему SYS CONNECT BY PATH возвращает результаты, которые и требовалось получить, как показано ранее:

select lpad(.,2*level,.)ename emp tree from emp start with mgr is null connect by prior empno = mgr

EMP TREE