Несколько примеров рекурсивных SQL-запросов для системы DIRECTUM

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

Пожалуй, одним определением и ограничусь, в сети масса информации на эту тему.

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

В стандартной поставке системы DIRECTUM есть ряд справочников, которые можно считать рекурсивными. Такие справочники содержат реквизит, сгенерированный по этому же справочнику (Подразделения, Поручения, РКК, Договоры и т.д.).

Косвенно рекурсивным можно считать справочники Работников (через Подразделения: в Работниках есть реквизит Подразделение, в Подразделении указан Руководитель - работник).

Кроме того, рекурсивной можно считать таблицу задач. Косвенно рекурсивной таблицу заданий (через задачи).

Построим несколько запросов на примере справочника Подразделения. Стандартный справочник Подразделения в DIRECTUM можно считать рекурсивным, так как в нем есть реквизит, который ссылается на сам справочник Подразделения: Головное подразделение. Фактически, любой из примеров легко переделать для другого справочника или таблиц ЗЗУ.

Пример 1

В качестве первого примера хочу привести общий запрос, который строит иерархию всех подразделений компании. В качестве подразделения нулевого уровня берется то, у которого не указано головное, далее по уровню подчиненности.

-----------------------------------------------------------------
-- Иерархия подразделений
-----------------------------------------------------------------
with DipartmentHiererhcy (
  ID,
  HightID, 
  Level)
as
(
-- Подразделение верхнего уровня (N = 0)
  select
    department.Analit,
    department.Podr,
    0 as Level
  from
    MBAnalit department
  where
    department.Podr is null
    and department.vid = <ИД типа справочника Подразделения>
  union ALL
-- Подчиненное подразделение (N + 1)
  select
    department.Analit,
    department.Podr,
    Level + 1
  from
    MBAnalit as department
      inner join DipartmentHiererhcy as hightdepartment
        on department.Podr = hightdepartment.ID
  where
    department.vid = <ИД типа справочника Подразделения>
)
-- Иерархия подразделений
select
  hierarchy.ID as [ИД],
  department.NameAn as [Подразделение],
  IsNull(hightdepartment.NameAn, '') as [Ведущее подразделение],
  Level as [Уровень]
from
  DipartmentHiererhcy hierarchy
    left join MBAnalit department
      on department.Analit = hierarchy.ID
   left join MBAnalit hightdepartment
      on hightdepartment.Analit = hierarchy.HightID

Результат этого запроса на тестовой базе: список подразделений с указанием ведущего и уровня в иерархии.

Пример 2

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

-----------------------------------------------------------------
-- Иерархия подразделений вниз от указанного
-----------------------------------------------------------------
with DipartmentHiererhcy (
  ID,
  HightID, 
  Level)
as
(
-- Подразделение верхнего уровня (N = 0)
  select
    department.Analit,
    department.Podr,
    0 as Level
  from
    MBAnalit department
  where
    department.Analit = <ИД текущего подразделения>
    and department.vid = <ИД типа справочника Подразделения>
  union ALL
-- Подчиненное подразделение (N + 1)
  select
    department.Analit,
    department.Podr,
    Level + 1
  from
    MBAnalit as department
      inner join DipartmentHiererhcy as hightdepartment
        on department.Podr = hightdepartment.ID
  where
    department.vid = <ИД типа справочника Подразделения>
)
-- Иерархия подразделений вниз от указанного
select
  hierarchy.ID as [ИД],
  department.NameAn as [Подразделение],
  IsNull(hightdepartment.NameAn, '') as [Ведущее подразделение],
  Level as [Уровень]
from
  DipartmentHiererhcy hierarchy
    left join MBAnalit department
      on department.Analit = hierarchy.ID
   left join MBAnalit hightdepartment
      on hightdepartment.Analit = hierarchy.HightID

Пример 3

Также может возникнуть необходимость получить данные по всем подчиненным указанного работника:

-----------------------------------------------------------------
-- Список подчиненных указанного работника
-----------------------------------------------------------------
with DipartmentHiererhcy (
  ID,
  HightID, 
  Level)
as
(
-- Подразделение верхнего уровня (N = 0)
  select
    department.Analit,
    department.Podr,
    0 as Level
  from
    MBAnalit department
  where
    department.FIO = <ИД руководителя из справочника Работники>
    and department.vid = <ИД типа справочника Подразделения>
  union ALL
-- Подчиненное подразделение (N + 1)
  select
    department.Analit,
    department.Podr,
    Level + 1
  from
    MBAnalit as department
      inner join DipartmentHiererhcy as hightdepartment
        on department.Podr = hightdepartment.ID
  where
    department.vid = <ИД типа справочника Подразделения>
)
-- Список подчиненных указанного работника
select distinct
  department.NameAn as [Подразделение],
  employee.NameAn as [Работник],
  IsNull(chiefemployee.NameAn, '') as [Руководитель],
  Level as [Уровень]
from
  DipartmentHiererhcy hierarchy
    left join MBAnalit department
      on department.Analit = hierarchy.ID
   left join MBAnalit hightdepartment
      on hightdepartment.Analit = hierarchy.HightID
   left join MBAnalit employee
     on department.Analit = employee.Podr and employee.Vid = <ИД типа справочника Работники>
   left join MBAnalit chiefemployee
     on department.FIO = chiefemployee.Analit and chiefemployee.Vid = <ИД типа справочника Работники>
where
  employee.Analit is not null

Пример 4

Обратный к примеру 3: цепочка руководителей указанного работника по иерархии вверх (например, может понадобиться для построения списка согласующих):

-----------------------------------------------------------------
-- Список руководителей указанного работника
-----------------------------------------------------------------
with DipartmentHiererhcy (
  ID,
  HightID, 
  Level)
as
(
-- Подразделение нижнего уровня (N = 0)
  select
    department.Analit,
    department.Podr,
    0 as Level
  from
    MBAnalit department
  where
    department.Analit = <ИД текущего Работника>
    and department.vid = <ИД типа справочника Подразделения>
  union ALL
-- Ведущее подразделение (N + 1)
  select
    department.Analit,
    department.Podr,
    Level + 1
  from
    MBAnalit as department
      inner join DipartmentHiererhcy as hightdepartment
        on department.Analit = hightdepartment.HightID
  where
    department.vid = <ИД типа справочника Подразделения>
)
-- Список руководителей указанного работника
select distinct
  department.NameAn as [Подразделение],
  IsNull(chiefemployee.NameAn, '') as [Руководитель],
  Level as [Уровень]
from
  DipartmentHiererhcy hierarchy
    left join MBAnalit department
      on department.Analit = hierarchy.ID
    left join MBAnalit chiefemployee
      on department.FIO = chiefemployee.Analit and chiefemployee.Vid = <ИД типа справочника Работники>

В качестве P.S.

 Не стоит забывать о возможности существования бесконечной рекурсии. Во избежание используйте:

OPTION (MAXRECURSION <Максимальный уровень вложенности>)