Созданную вспомогательную таблицу заполним данными, используя уникальные идентификаторы, автоматически назначенные узлам иерархии при заполнении базовой таблицы.
NSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',0);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'BB31DC0F-BB57-48FF-B768-79D205F17F33',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'07EDFC1DE-BE2A-38BC86BA9EA2',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'07EDFC1DE-BE2A-38BC86BA9EA2',
'06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'777A3A0EB9-8B9D-325230D48A3A',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'777A3A0EB9-8B9D-325230D48A3A',
'06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'CF8D4BA4-89EB12-DDF2987D5949',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'CF8D4BA4-89EB12-DDF2987D5949',
'BB31DC0F-BB57-48FF-B768-79D205F17F33',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'9A42488B-536B-4269-A90C-BEB',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'9A42488B-536B-4269-A90C-BEB',
'BB31DC0F-BB57-48FF-B768-79D205F17F33',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'8E8CDC4F-A5A1-3DF2C25963AD',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'8E8CDC4F-A5A1-3DF2C25963AD',
'BB31DC0F-BB57-48FF-B768-79D205F17F33',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'D9826E05-BFD1-4845-AEFC-215C2E5BA78B',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'D9826E05-BFD1-4845-AEFC-215C2E5BA78B',
'06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'D9826E05-BFD1-4845-AEFC-215C2E5BA78B',
'07EDFC1DE-BE2A-38BC86BA9EA2',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'AF1A9E1A-4747-4BC1-A5F5-FA30272BD4A8',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'AF1A9E1A-4747-4BC1-A5F5-FA30272BD4A8',
'06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'AF1A9E1A-4747-4BC1-A5F5-FA30272BD4A8',
'777A3A0EB9-8B9D-325230D48A3A',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'AEB398A7-F653-4BE8-8844-AE9CAEC1A26F',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'AEB398A7-F653-4BE8-8844-AE9CAEC1A26F',
'06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'AEB398A7-F653-4BE8-8844-AE9CAEC1A26F',
'777A3A0EB9-8B9D-325230D48A3A',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'2520E3D9-B40F-4A6A-ADD8-A334CABCFBB2',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'2520E3D9-B40F-4A6A-ADD8-A334CABCFBB2',
'06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'2520E3D9-B40F-4A6A-ADD8-A334CABCFBB2',
'777A3A0EB9-8B9D-325230D48A3A',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'561D8DECC-BA0E-CE4CC204705B',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'561D8DECC-BA0E-CE4CC204705B',
'BB31DC0F-BB57-48FF-B768-79D205F17F33',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'561D8DECC-BA0E-CE4CC204705B',
'CF8D4BA4-89EB12-DDF2987D5949',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'0FBD7863-B4B5-456A-8FAC-63B972AAD010',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'0FBD7863-B4B5-456A-8FAC-63B972AAD010',
'BB31DC0F-BB57-48FF-B768-79D205F17F33',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'0FBD7863-B4B5-456A-8FAC-63B972AAD010',
'9A42488B-536B-4269-A90C-BEB',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'5BDF65BEF3-BBB1-B3BA8B48287B',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'5BDF65BEF3-BBB1-B3BA8B48287B',
'BB31DC0F-BB57-48FF-B768-79D205F17F33',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'5BDF65BEF3-BBB1-B3BA8B48287B',
'9A42488B-536B-4269-A90C-BEB',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'FC579270-CADA3-BEA0F5DB6E24',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'FC579270-CADA3-BEA0F5DB6E24',
'BB31DC0F-BB57-48FF-B768-79D205F17F33',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'FC579270-CADA3-BEA0F5DB6E24',
'8E8CDC4F-A5A1-3DF2C25963AD',1);
Таблица 8
Вспомогательная таблица T_Helper после заполнения данными
UID | ParentID | Level |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 0 |
06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 1 |
BB31DC0F-BB57-48FF-B768-79D205F17F33 | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 1 |
07EDFC1DE-BE2A-38BC86BA9EA2 | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 2 |
07EDFC1DE-BE2A-38BC86BA9EA2 | 06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F | 1 |
777A3A0EB9-8B9D-325230D48A3A | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 2 |
777A3A0EB9-8B9D-325230D48A3A | 06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F | 1 |
CF8D4BA4-89EB12-DDF2987D5949 | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 2 |
CF8D4BA4-89EB12-DDF2987D5949 | BB31DC0F-BB57-48FF-B768-79D205F17F33 | 1 |
9A42488B-536B-4269-A90C-BEB | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 2 |
9A42488B-536B-4269-A90C-BEB | BB31DC0F-BB57-48FF-B768-79D205F17F33 | 1 |
8E8CDC4F-A5A1-3DF2C25963AD | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 2 |
8E8CDC4F-A5A1-3DF2C25963AD | BB31DC0F-BB57-48FF-B768-79D205F17F33 | 1 |
D9826E05-BFD1-4845-AEFC-215C2E5BA78B | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 3 |
D9826E05-BFD1-4845-AEFC-215C2E5BA78B | 06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F | 2 |
D9826E05-BFD1-4845-AEFC-215C2E5BA78B | 07EDFC1DE-BE2A-38BC86BA9EA2 | 1 |
AF1A9E1A-4747-4BC1-A5F5-FA30272BD4A8 | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 3 |
AF1A9E1A-4747-4BC1-A5F5-FA30272BD4A8 | 06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F | 2 |
AF1A9E1A-4747-4BC1-A5F5-FA30272BD4A8 | 777A3A0EB9-8B9D-325230D48A3A | 1 |
AEB398A7-F653-4BE8-8844-AE9CAEC1A26F | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 3 |
AEB398A7-F653-4BE8-8844-AE9CAEC1A26F | 06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F | 2 |
AEB398A7-F653-4BE8-8844-AE9CAEC1A26F | 777A3A0EB9-8B9D-325230D48A3A | 1 |
2520E3D9-B40F-4A6A-ADD8-A334CABCFBB2 | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 3 |
2520E3D9-B40F-4A6A-ADD8-A334CABCFBB2 | 06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F | 2 |
2520E3D9-B40F-4A6A-ADD8-A334CABCFBB2 | 777A3A0EB9-8B9D-325230D48A3A | 1 |
561D8DECC-BA0E-CE4CC204705B | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 3 |
561D8DECC-BA0E-CE4CC204705B | BB31DC0F-BB57-48FF-B768-79D205F17F33 | 2 |
561D8DECC-BA0E-CE4CC204705B | CF8D4BA4-89EB12-DDF2987D5949 | 1 |
0FBD7863-B4B5-456A-8FAC-63B972AAD010 | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 3 |
0FBD7863-B4B5-456A-8FAC-63B972AAD010 | BB31DC0F-BB57-48FF-B768-79D205F17F33 | 2 |
0FBD7863-B4B5-456A-8FAC-63B972AAD010 | 9A42488B-536B-4269-A90C-BEB | 1 |
5BDF65BEF3-BBB1-B3BA8B48287B | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 3 |
5BDF65BEF3-BBB1-B3BA8B48287B | BB31DC0F-BB57-48FF-B768-79D205F17F33 | 2 |
5BDF65BEF3-BBB1-B3BA8B48287B | 9A42488B-536B-4269-A90C-BEB | 1 |
FC579270-CADA3-BEA0F5DB6E24 | 7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 | 3 |
FC579270-CADA3-BEA0F5DB6E24 | BB31DC0F-BB57-48FF-B768-79D205F17F33 | 2 |
FC579270-CADA3-BEA0F5DB6E24 | 8E8CDC4F-A5A1-3DF2C25963AD | 1 |
Обратите внимание. Аналогично способу со структурой со ссылкой на предка столбец «Указатель на предка узла» содержит список всех узлов дерева, имеющих потомков.
Данная модель позволяет проще, чем в случае рекурсивного метода, но несколько сложнее, чем в случае метода правого и левого коэффициентов (из-за необходимости связывания двух таблиц), выполнять практически все выборки, специфические для иерархий.
Приведём несколько простых примеров таких выборок.
Выбор всех потомков для заданного узла (в примере это узел B) может быть осуществлен следующим предложением SQL:
SELECT T_Base. Node
FROM T_Base, T_Helper
WHERE T_Base. ID=T_Helper. UID and
T_Helper. ParentID=(SELECT ID FROM T_Base
WHERE Node='B');
Результат данного запроса представлен на рис. 76.
Рис. 76. Выборка всех потомков узла B
Выбор всех предков для заданного узла (в примере это узел J) осуществляется следующим образом:
SELECT T_Base. Node, T_Helper. Level
FROM T_Base, T_Helper
WHERE T_Base. ID=T_Helper. ParentID and
T_Helper. UID=(SELECT ID FROM T_Base
WHERE Node='J')
ORDER BY T_Helper. Level DESC;
Результат выполнения этого запроса представлен на рис. 77.
Рис. 77. Выбор всех предков узла J
Обратите внимание на схожесть этих запросов. Сравните эти запросы с решением данных задач в случае использования рекурсивной модели иерархии.
Выбор узлов, не имеющих потомков, реализуется, например, следующим образом:
SELECT DISTINCT T_Base. Node FROM T_Base, T_Helper
WHERE T_Base. ID=T_Helper. UID and T_Helper. UID NOT IN (
SELECT DISTINCT ParentID FROM T_Helper);
Результат выполнения этого запроса представлен на рис. 78.
Рис. 78. Узлы, не имеющие потомков
Обратите внимание, что данный запрос очень похож на запрос, который мы использовали для решения той же задачи в случае рекурсивной модели иерархии.
Теперь рассмотрим задачу отыскания ближайшего общего предка для двух заданных узлов. Пусть выбраны узлы I и K. Как видно на рис. 8, их ближайшим общим предком является узел B. Получим полный список общих предков для узлов I и K (рис. 79):
SELECT T_Base. Node, T_Helper. Level, T_Helper. ParentID
FROM T_Base, T_Helper
WHERE T_Base. ID=T_Helper. ParentID and
T_Helper. UID=(SELECT ID FROM T_Base WHERE Node='K') AND T_Helper. ParentID IN (
SELECT T_Helper. ParentID
FROM T_Base, T_Helper
WHERE T_Base. ID=T_Helper. ParentID and
T_Helper. UID=(
SELECT ID FROM T_Base WHERE Node='I'));
Рис. 79. Полный список общих предков узлов I и K
Из этого списка надо оставить единственную строку, в которой значение в столбце Level минимально. Окончательно получаем (рис. 80):
SELECT T_Base. Node, T_Helper. Level, T_Helper. ParentID
FROM T_Base, T_Helper
WHERE T_Base. ID=T_Helper. ParentID AND
T_Helper. UID=(SELECT ID FROM T_Base WHERE Node='K') AND
T_Helper. ParentID IN
(SELECT T_Helper. ParentID
FROM T_Base, T_Helper
WHERE T_Base. ID=T_Helper. ParentID AND
T_Helper. UID=(SELECT ID FROM T_Base WHERE Node='I')) AND
T_Helper. Level=(SELECT MIN(T_Helper. Level)
FROM T_Base, T_Helper
WHERE T_Base. ID=T_Helper. ParentID AND
T_Helper. UID=(SELECT ID FROM T_Base WHERE Node='K') AND
T_Helper. ParentID IN
(SELECT T_Helper. ParentID
FROM T_Base, T_Helper
WHERE T_Base. ID=T_Helper. ParentID AND
T_Helper. UID=(SELECT ID FROM T_Base WHERE Node='I')));
Рис. 80. Ближайший общий предок узлов I и K
Поддержка целостности структуры иерархии требует дополнительныx средств.
Приведём пример хранимой процедуры для добавления листьев в дерево (триггер и в этом случае неприменим, так как надо указывать непосредственного предка добавляемого узла):
CREATE PROCEDURE AddNode (@Parent VARCHAR, @New VARCHAR) AS
-- Добавляем лист
-- ВНИМАНИЕ! Тут должна быть проверка
-- входного параметра!
-- После каждой операции необходима проверка ошибок,
-- отсутствующая в данном примере!
BEGIN
DECLARE @ParentID uniqueidentifier;
DECLARE @NewID uniqueidentifier;
-- Транзакция
BEGIN TRANSACTION AddNodeTransaction;
-- Добавляем строку нового узла в базовую таблицу
INSERT INTO T_Base(Node) VALUES (@New);
-- Получаем уникальный идентификатор нового элемента
SELECT @NewID=ID FROM T_Base WHERE Node=@New;
-- Получаем уникальный идентификатор
-- родительского элемента
SELECT @ParentID=ID FROM T_Base WHERE Node=@Parent;
-- ВНИМАНИЕ! Тут должна быть проверка
|
Из за большого объема этот материал размещен на нескольких страницах:
1 2 3 4 5 6 7 |
