Блог

Главная - Вопросы по программированию - Логика в дереве Oracle

Логика в дереве Oracle

#sql #oracle #oracle11g #oracle10g

Вопрос:

Ниже приведена структура таблицы :

REF_ID НЕ НУЛЕВОЕ ЧИСЛО
REF_TYPE_ID НЕ НУЛЕВОЕ ЧИСЛО
PARENT_REF_ID ЧИСЛО
REF_ ЗНАЧЕНИЕ НЕ РАВНО НУЛЮ VARCHAR2(255)

Следующий запрос дал следующий результат, но мне может понадобиться результат :

 WITH
    refs (ref_id,
          ref_type_id,
          parent_ref_id,
          ref_value)
    AS
        (SELECT 501, 1, NULL, 207 FROM DUAL
         UNION ALL
         SELECT 502, 2, 501, 4 FROM DUAL
         UNION ALL
         SELECT 503, 3, 502, 1 FROM DUAL)
    SELECT CONNECT_BY_ROOT r.ref_id as starting_ref_id,
           TRIM (
               ',' FROM
                   SYS_CONNECT_BY_PATH (
                          CASE r.ref_type_id
                              WHEN 1 THEN 'article '
                              WHEN 2 THEN 'par '
                              WHEN 3 THEN '('
                              WHEN 4 THEN 'point '
                              WHEN 5 THEN 'sous '
                              WHEN 6 THEN NULL
                              WHEN 8 THEN NULL
                              ELSE '/'
                          END
                       || r.ref_id,
                       ','))    AS ref_label
      FROM refs r
     WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR r.parent_ref_id = r.ref_id;

Вывод из запроса :

НАЧАЛЬНАЯ МЕТКА_REF_ID REF_LABEL

            501 article 501
           502 par 502,article 501
           503 (503,par 502,article 501

Ожидаемый результат:

НАЧАЛЬНАЯ МЕТКА_REF_ID REF_LABEL

            501 article 501
           502 article 501,par 502
           503 article 501,par 502,(503,

Ответ №1:

Добавлен реверс для выбора

 WITH
refs (ref_id,
      ref_type_id,
      parent_ref_id,
      ref_value)
AS
    (SELECT 501, 1, NULL, 207 FROM DUAL
     UNION ALL
     SELECT 502, 2, 501, 4 FROM DUAL
     UNION ALL
     SELECT 503, 3, 502, 1 FROM DUAL)
SELECT CONNECT_BY_ROOT r.ref_id as starting_ref_id,
       TRIM (
           ',' FROM
           reverse( --reverse path order (also reverts letters in words)
               SYS_CONNECT_BY_PATH (
                 
                     reverse( --put letters in words and numbers in ids back in the right order
                          CASE r.ref_type_id
                          WHEN 1 THEN 'article '
                          WHEN 2 THEN 'par '
                          WHEN 3 THEN '('
                          WHEN 4 THEN 'point '
                          WHEN 5 THEN 'sous '
                          WHEN 6 THEN NULL
                          WHEN 8 THEN NULL
                          ELSE '/'
                      END
                   || r.ref_id),
                   ',')
                   
                   ) )   AS ref_label
  FROM refs r
 WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR r.parent_ref_id = r.ref_id

Результат:

Комментарии:

1. Решение делает то, что запросил ОП. Однако с этим есть две проблемы. Во-первых, reverse не документирован (и, следовательно, не поддерживается); это нормально для некритичных приложений, но не должно использоваться в производстве в важных приложениях. Во — вторых, существующий запрос операции изначально ошибочен-он не начинается с корня и не проходит по дереву вниз, он идет в противоположном направлении. Этот ответ предлагает дополнительное исправление в том же ошибочном решении, вместо устранения основной причины всего этого (исправления самого запроса).

Ответ №2:

Вы делаете все это очень неестественным образом.

Вы должны начать с корня (где находится идентификатор родителя null ) и идти «вниз» от него, а не наоборот. Тогда вам не нужна where оговорка, вам нужна start with оговорка; и connect by условие должно идти в обратном направлении. Тогда вам не нужно вносить какие-либо другие изменения в запрос.

Подобный этому:

 WITH
    refs (ref_id,
          ref_type_id,
          parent_ref_id,
          ref_value)
    AS
        (SELECT 501, 1, NULL, 207 FROM DUAL
         UNION ALL
         SELECT 502, 2, 501, 4 FROM DUAL
         UNION ALL
         SELECT 503, 3, 502, 1 FROM DUAL)
    SELECT CONNECT_BY_ROOT r.ref_id as starting_ref_id,
           TRIM (
               ',' FROM
                   SYS_CONNECT_BY_PATH (
                          CASE r.ref_type_id
                              WHEN 1 THEN 'article '
                              WHEN 2 THEN 'par '
                              WHEN 3 THEN '('
                              WHEN 4 THEN 'point '
                              WHEN 5 THEN 'sous '
                              WHEN 6 THEN NULL
                              WHEN 8 THEN NULL
                              ELSE '/'
                          END
                       || r.ref_id,
                       ','))    AS ref_label
      FROM refs r
START WITH parent_ref_id is null
CONNECT BY PRIOR ref_id = parent_ref_id;
Метки: