Извлечь элемент из списка в одной строке, в нужном порядке
Задам вопрос в форме упрощенной задачи от реального случая.
Дано: таблица tab_1 со столбцом col_1 строкового типа, в котором есть перечисление элементов. В качестве примера возьмем:
WITH tab_1
AS (SELECT '4,5,44,46,3' col_1 FROM DUAL UNION ALL
SELECT '7,44' col_1 FROM DUAL UNION ALL
SELECT '67,32,5' col_1 FROM DUAL UNION ALL
SELECT '26,34,44' col_1 FROM DUAL)
SELECT col_1
FROM tab_1;
Наша задача: из каждой строки в столбце col_1 извлечь только один элемент, который также входит в контрольный список '32,34,5,44'. Если в строке есть два элемента из контрольного списка, то взять тот, что приоритетнее (приоритет такой же 32,34,5,44). Ожидаемый ответ:
| result_val |
|------------|
| 5 |
| 44 |
| 32 |
| 34 |
Вопрос: какой запрос следует сделать из tab_1?
Ответы (1 шт):
У меня получилось такое решение в контексте поставленной задачи. Характерно, что здесь задействован сплит строк через регулярное выражение, но именно всех строк в наборе (а не как в большинстве вопросов на stackoverflow только одной строки на манер функции).
WITH tab_1
AS (SELECT '4,5,44,46,3' col_1 FROM DUAL UNION ALL
SELECT '7,44' col_1 FROM DUAL UNION ALL
SELECT '67,32,5' col_1 FROM DUAL UNION ALL
SELECT '26,34,44' col_1 FROM DUAL)
SELECT col_1
, CASE MAX(CASE parts
WHEN '32' THEN 4
WHEN '34' THEN 3
WHEN '5' THEN 2
WHEN '44' THEN 1
ELSE -1
END)
WHEN 4 THEN 32
WHEN 3 THEN 34
WHEN 2 THEN 5
WHEN 1 THEN 44
ELSE -1
END
result_val
FROM ( SELECT REGEXP_SUBSTR(t.col_1, '[^,]+', 1, LEVEL) AS parts
, t.*
FROM tab_1 t
CONNECT BY PRIOR t.col_1 = t.col_1
AND PRIOR SYS_GUID() IS NOT NULL
AND LEVEL < REGEXP_COUNT(t.col_1, '([^,]*)(,|$)'))
GROUP BY col_1;