Деревья в SQL
Страница 3. Агрегатные функции в деревьях
Страница 3 из 6
 

Агрегатные функции в деревьях

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

SELECT P1.emp, SUM(P2.salary) AS payroll
FROM Personnel AS P1, Personnel AS P2
WHERE P2.lft BETWEEN P1.lft AND P1.rgt
GROUP BY P1.emp;

emppayroll 
========== 
Albert7800.00 
Bert1650.00 
Charles3250.00 
Diane1900.00 
Edward 750.00 
Fred1600.00 
George 750.00 
Heidi1000.00 
Igor 500.00 
Jim 300.00 
Kathy 100.00 
Larry 100.00 
Mary 100.00 
Ned 100.00

Следующий запрос будет брать уволенного служащего как параметр и удалять поддерево, расположенное под ним/ней. Уловка в этом запросе - то, что Вы используете ключ, но Вы должны заставить работать левые и правые значения. Ответ - набор скалярных подзапросов: 

DELETE FROM Personnel
WHERE lft BETWEEN
(SELECT lft FROM Personnel WHERE emp = :downsized)
AND
(SELECT rgt FROM Personnel WHERE emp = :downsized);

Проблема состоит в том, что после этого запроса появляются промежутки в последовательности номеров множеств. Это не мешает выполнять большинство запросов к дереву, т.к. свойство вложения сохранено. Это означает, что Вы можете использовать предикат BETWEEN в ваших запросах, но другие операции, которые зависят от плотности номеров, не будут работать в дереве с промежутками. Например, Вы не сможете находить листья, используя предикат (right-left=1), и не сможете найти число узлов в поддереве, используя значения left и right его корня.

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

CREATE PROCEDURE DropTree (downsized IN CHAR(10) NOT NULL)
BEGIN ATOMIC
DECLARE dropemp CHAR(10) NOT NULL;
DECLARE droplft INTEGER NOT NULL;
DECLARE droprgt INTEGER NOT NULL;

--Теперь сохраним данные поддерева: 

SELECT emp, lft, rgt
INTO dropemp, droplft, droprgt 
FROM Personnel
WHERE emp = downsized;

--Удаление, это просто...

DELETE FROM Personnel
WHERE lft BETWEEN droplft and droprgt;

--Теперь уплотняем промежутки: 

UPDATE Personnel 
SET lft = CASE 
WHEN lft > droplf 
THEN lft - (droprgt - droplft + 1) 
ELSE lft END, 
rgt = CASE 
WHEN rgt > droplft 
THEN rgt - (droprgt - droplft + 1) 
ELSE rgt END;END;

Реальная процедура должна иметь обработку ошибок, но я оставляю это как упражнение для читателя.
 
Следующая статья »