Правильная ссылка на эту страницу
http://az-design.ru/Support/DataBase/DBTree2/2D00.shtml

Полезные запросы при работе с древовидными структурами

Архангельский Андрей

Движение по дереву вверх

       Часто выбирая какое-то значение из одной таблицы требуется получить родителей этого элемента в другой таблице, для того чтобы построить дерево с новой точки и как правило выше на один или несколько уровней. Это можно назвать "Движением по дереву вверх". Простой запрос с использованием оператора IN позволить это сделать.

ibQuery.Close;    ibQuery.SQL.Clear;
ibQuery.SQL.Add('Select GoodsNo,GdCntFrm,GdFirmCod,GdNames from Goods');
ibQuery.SQL.Add(' where GoodsNo in (Select distinct GdPrnFrm from Goods');
ibQuery.SQL.Add(' where GoodsNo in (Select distinct NSCarModel from NSKompleks');
ibQuery.SQL.Add(' where NSCarModel<>0))');
ibQuery.Open; 

       Внутренний запрос "Select distinct NSCarModel from NSKompleks..." выбирает модели автомобилей, для который в таблице NSKompleks есть комплексы работ для ремонта.
       Запрос на один уровень выше "Select distinct GdPrnFrm from Goods where GoodsNo in(..." выбирает группы автомобилей для полученных моделей.
       И, наконец, самый верхний запрос выбирает заводские каталоги, в которых содержаться группы автомобилей, для моделей которых есть комплексы работ для ремонта.
       Конечно, можно было бы сразу строить дерево начиная с заводских каталогов, но каталогов и заводов может быть много больше и не для всех определены комплексы работ. В этом случае пользователь бы получал дерево, в котором многие ветви были бы излишни, а пользователь бы тыкался в модели, для которых работы не определены (пока).
       Необходимо только заметить что, с каждым IN время выполнения запроса возрастает многократно и для интерактивной работы это создает проблемы. Поэтому, после измерений, для реальной программы был принят следующий вариант с использованием вспомогательной таблицы:
       Первый вложенный запрос вставляет свои результаты в вспомогательную таблицу:

ibQuery.Close;    ibQuery.SQL.Clear;
ibQuery.SQL.Add('Insert into GoodsLev(GoodsNo,GdsTree)');
ibQuery.SQL.Add('Select distinct g.GdPrnFrm,-1 from Goods g, NSKompleks k');
ibQuery.SQL.Add(' where g.GoodsNo=k.NSCarModel and k.NSCarModel<>0');
ibQuery.ExecSQL;

       Второй вложенный запрос использует эти результаты и создает в вспомогательной таблице второй набор значений:

ibQuery.Close;    ibQuery.SQL.Clear;
ibQuery.SQL.Add('Insert into GoodsLev(GoodsNo,GdsTree)');
ibQuery.SQL.Add('Select distinct g.GdPrnFrm,-2 from Goods g, GoodsLev l');
ibQuery.SQL.Add(' where g.GoodsNo=l.GoodsNo and l.GdsTree=-1');
ibQuery.ExecSQL;

       И, наконец, третий запрос, используя второй набор данных, строит финальный набор значений, который и используется в дальнейшем.

ibKomplTree.Close;    ibKomplTree.SQL.Clear;
ibKomplTree.SQL.Add('Select g.GoodsNo,g.GdCntFrm,g.GdFirmCod,g.GdNames');
ibKomplTree.SQL.Add(' from Goods g, GoodsLev L');
ibKomplTree.SQL.Add(' where g.GoodsNo=l.GoodsNo and L.GdsTree=-2');
ibKomplTree.Open; 

       Как показали измерения на реальной БД, второй вариант уменьшил общее время выполнения запроса с 3 минут до 1.5 секунд.

Получение самого верхнего родителя

       Это случай, когда выбранные значения в таблице NSKompleks оказываются на разных уровнях дерева в таблице Goods. Но пользователю необходимо предложить некоторую точку на дереве, начиная с которой он мог бы начинать свой выбор не затрачивая лишних усилий. Это и называется "получение верхнего родителя".

  ibKomplTree.Close;    ibKomplTree.SQL.Clear;
  ibKomplTree.SQL.Add('Select distinct GdPrnFrm from Goods');
  ibKomplTree.SQL.Add(' where GoodsNo in (Select distinct g.GdPrnFrm');
  ibKomplTree.SQL.Add(' from Goods g, NSKompleks k');
  ibKomplTree.SQL.Add(' where g.GoodsNo=k.NSCarDetal');
  ibKomplTree.SQL.Add(' and k.NSCarModel='+IntToStr(GrpModel)+')');
  ibKomplTree.Open;    RowCount := ibKomplTree.RecordCount;
  ibQuery.Close;    ibQuery.SQL.Clear;
  ibQuery.SQL.Add('Delete from GoodsLev');
  ibQuery.ExecSQL;
  If RowCount>0 then Begin
     for x:=0 to RowCount-1 do Begin
         z := ibKomplTree.FieldValues['GdPrnFrm'];
         ibQuery.Close;    ibQuery.SQL.Clear;
         ibQuery.SQL.Add('Insert into GoodsLev(GoodsNo,GdsLevel,GdsTree)');
         ibQuery.SQL.Add('Select '+IntToStr(z)+',Min(GdsLev),4 ');
         ibQuery.SQL.Add('from GDParent('+IntToStr(z)+',4,0)');
         ibQuery.ExecSQL;
         ibKomplTree.Next;
     end;
  end;
  ibKomplTree.Close;    ibKomplTree.SQL.Clear;
  ibKomplTree.SQL.Add('Select GoodsNo,GdCntFrm,GdFirmCod,GdNames from Goods');
  ibKomplTree.SQL.Add(' where GdPrnFrm in (Select GoodsNo from GoodsLev');
  ibKomplTree.SQL.Add(' where GdsLevel = (select Max(GdsLevel) from GoodsLev');
  ibKomplTree.SQL.Add(' order by GdFirmCod,GdNames');
  ibKomplTree.Open;

       Первый запрос получает родителей всех деталей, которые содержаться в таблице NSKompleks. Так как это родители, то их число небольшое.
       Дальше в цикле для каждого родителя получаем его уровень в дереве и записываем результат в таблицу GdsLevel.
       И, наконец, выбрав из этой таблицы максимальное значение, получаем исходную точку на дереве, начиная с которой можно организовывать интерфейс. Подробнее об этом см. "Расчет трудоемкости ремонта автомобилей".

 

Подсчет суммарной зарплаты

       Одним из полезных свойств дерева является ссуммирование значений отдельных полей потомков выбранного узла. Так на основе дерева можно подсчитать суммарную зарплату подразделения в нашем примере.
       Для этого необходимо использовать вышеописанную процедуру PeopleGetChild, которая находит всех потомков выбранного узла. Запрос при этом должен выглядеть примерно так:

Select Sum(PSalary) from People
 where PID in (Select Chld from PeopleGetChild(:Prnt,0));

       Для демонстрации этой возможности используем процедуру, присоединенную к пункту меню:

procedure TForm1.mnSumSalaryClick(Sender: TObject); {Example01}
Var
   SumSal : Double;
   iPrnt : Integer;
begin
   iPrnt := tvPeople.Selected.ImageIndex; // Получение ID узла
   qrExeProc.Close;
   qrExeProc.SQL.Clear;
   qrExeProc.SQL.Add('Select Sum(PSalary) as SumSal from People');
   qrExeProc.SQL.Add('where PID in (Select Chld from PeopleGetChild('
                     +IntToStr(iPrnt)+',0))');
   qrExeProc.Open;
   SumSal := qrExeProc.FieldValues['SumSal'];
   ShowMessage('Зарплата подразделения = '+Format('%8.2f',[SumSal]));
end;

       Запустив программу Example01 и выбрав соответствующий узел, нажимаем на правую кнопку мыши для вызова контекстного меню:


Рис.1-9 Выбор функции расчета зарплаты подразделения

       После выполнения этой функции выводится результат:


Рис.1-10 Результат функции расчета зарплаты подразделения

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

© 01.08.2009, Архангельский А.Г.

<<Пред. Оглавление
Начало раздела
След.>>




Дата последнего изменения:
Thursday, 21-Aug-2014 09:10:44 MSK


Постоянный адрес статьи:
http://az-design.ru/Support/DataBase/DBTree2/2D00.shtml