Блог

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

 CREATE TABLE WORKGROUPS  ( 
    WORKGROUPID         NUMBER NULL,
    NAME                VARCHAR2(100) NOT NULL,
    PARENTWORKGROUPID   NUMBER NOT NULL,
    WORKGROUPLEVEL      CHAR(1) DEFAULT 1 NOT NULL,
    CONSTRAINT WORKGROUPS_PK PRIMARY KEY(WORKGROUPID)
)
  

Например, может быть три уровня рабочих групп:

 ID 1 - Sales (PARENTWORKGROUPID = 0)
ID 2 - Business Sales (PARENTWORKGROUPID = 1)
ID 3 - West Coast B2B (PARENTWORKGROUPID = 2)
  

Итак, родительский элемент 3 равен 2, родительский элемент 2 равен 1, а 1 — рабочая группа верхнего уровня без родительского элемента, поэтому мы используем 0.

Теперь у нас есть таблица ЗАДАЧ. Каждая строка ЗАДАЧ имеет столбец WORKGROUPID, который указывает на WORKGROUPID в таблице WORKGROUPS.

Мне нужно написать запрос, который возвращает все ЗАДАЧИ, относящиеся к данной рабочей группе верхнего уровня, например, все, что находится в разделе Sales (в приведенном выше примере это могут быть идентификаторы рабочей группы 1, 2 или 3. По сути, это рекурсивный запрос.

Я могу придумать несколько способов сделать это, используя ЛЕВОЕ СОЕДИНЕНИЕ для проверки каждого уровня, но я бы предпочел держаться подальше от решений, которые жестко задают количество уровней, поскольку база данных рассчитана на любое количество уровней. Любое другое решение, которое я могу придумать, связано с изменением схемы таблицы, чего я не могу сделать в данный момент. Есть идеи? Спасибо!

Начиная с Oracle 11gR2, поддерживается ANSI-рекурсивный синтаксис WITH, который является альтернативой START WITH/CONNECT BY:

 WITH wgs ( workgroupid, name ) AS
(
    SELECT workgroupid, name FROM workgroups WHERE workgroupid = :top-lev-dept
     UNION ALL
    SELECT w.workgroupid, w.name FROM workgroups w, wgs WHERE parentworkgroupid = wgs.workgroupid
)
SELECT ...
  

Ключевым моментом здесь является:

 WITH view (column_definition) AS
(
    SELECT root rows
     UNION ALL
    SELECT sub rows FROM table JOIN view ON recursion condition
)
  

У Oracle, похоже, есть своя собственная версия рекурсии (я использую SQL Server и DB2, которые используют общие табличные выражения для управления рекурсией), но я думаю, что это должно получить то, что вы хотите:

 WITH WGS (
    WORKGROUPID,
    NAME
) AS (
    SELECT WORKGROUPID,
        NAME
    FROM WORKGROUPS
    START WITH WORKGROUPID = :top-lev-dept
    CONNECT BY WORKGROUPID = PRIOR PARENTWORKGROUPID
)

SELECT DISTINCT T.*
FROM TASKS
INNER JOIN WGS W
    ON (T.WORKGROUPID = W.WORKGROUPID)
  

Вы, очевидно, перейдете :top-lev-dept в отдел, который ищете.

Если это не так, вы можете проверить справочную страницу Oracle по иерархическим запросам. Это может помочь вам начать в правильном направлении…

Вы захотите изучить START WITH/CONNECT BY синтаксис.

Что-то вроде:

 select * from workgroups
start with parentworkgroupid = 0
connect by prior workgroupid = parentworkgroupid;
  

Полностью непроверенный, но я думаю, что это поможет вам начать.

Надеюсь, это поможет.