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

Written on . Posted in Язык запросов SQL

ОГЛАВЛЕНИЕ

Страница 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;

	emp	payroll  
	===	=======  
	Albert	7800.00  
	Bert	1650.00  
	Charles	3250.00  
	Diane	1900.00  
	Edward	 750.00  
	Fred	1600.00  
	George	 750.00  
	Heidi	1000.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;

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

базы данных форум