#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;