Обсуждение

DB2, PostgreSQL, MySQL и SQL Server

Для выявления концевых, корневых узлов и узлов ветвления в решении просто применяются правила, описанные в разделе Задача . Первый шаг - дать определение тому, какой узел считается концевым узлом. Если служащий не является руководителем (никто не находится в его подчинении), он - концевой узел. Первый скалярный подзапрос, IS LEAF, показан ниже:

select e.ename,

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

from emp e order by 2 desc

ENAME IS LEAF

SMITH 1

ALLEN 1

WARD 1

ADAMS 1

TURNER 1

MARTIN 1

JAMES 1

MILLER 1

JONES 0

BLAKE 0

CLARK 0

FORD 0

SCOTT 0

KING 0

Поскольку IS LEAF должен возвращать 0 или 1, к операции COUNT(*) необходимо применить функцию SIGN. В противном случае для концевых строк мы получим 14, а не 1. В качестве альтернативы для подсчета можно использовать таблицу с одной строкой, ведь нам требуется возвращать только 0 или 1. Например:

select e.ename,

(select count(*) from t1 d where not exists

(select null from emp f where f.mgr = e.empno)) as is leaf

from emp e order by 2 desc

ENAME IS LEAF

SMITH

ALLEN

WARD

ADAMS

TURNER

MARTIN

JAMES

MILLER

JONES

BLAKE

CLARK

FORD

SCOTT KING

Следующий шаг - найти узлы ветвления. Если служащий является руководителем (имеет подчиненных), а также находится в подчинении у другого служащего, он является узлом ветвления. Результаты скалярного подзапроса IS BRANCH показаны ниже:

select e.ename,

(select sign(count(*)) from emp d where d.mgr = e.empno and e.mgr is not null) as is branch from emp e order by 2 desc

ENAME

IS BRANCH

JONES

BLAKE

SCOTT

CLARK

FORD

SMITH

TURNER

MILLER

JAMES

ADAMS

KING

ALLEN

MARTIN

WARD

Опять же необходимо применить функцию SIGN к операции COUNT(*). В противном случае для узлов ветвления будут получены (потенциально) значения больше 1. Как и в скалярном подзапросе IS LEAF, избежать SIGN можно с помощью таблицы в одну строку. В следующем решении используется такая таблица под названием dual:1

Таблица dual - стандартная таблица для Oracle; во всех остальных случаях придется создавать таблицу, которая будет содержать одну строку; поэтому в примере указана tl, а не dual. - Примеч. науч.ред.

select e.ename,

(select count(*) from t1 t where exists ( select null from emp f where f.mgr = e.empno and e.mgr is not null)) as is branch

from emp e order by 2 desc

ENAME

IS BRANCH

JONES

BLAKE

SCOTT

CLARK

FORD

SMITH

TURNER

MILLER

JAMES

ADAMS

KING

ALLEN

MARTIN

WARD

Последний шаг - найти корневые узлы. Корневой узел определен как служащий, являющийся руководителем, но не находящийся ни в чьем подчинении. В таблице EMP только служащий KING удовлетворяет этому условию и является корневым узлом. Скалярный подзапрос IS ROOT показан ниже:

select e.ename,

(select sign(count(*)) from emp d where d.empno = e.empno

and d.mgr is null) as is root from emp e order by 2 desc

ENAME

IS ROOT

KING

SMITH

ALLEN

WARD

JONES

TURNER

JAMES

MILLER

FORD

ADAMS

MARTIN