Создайте представление таблицы со столбцом, который имеет несколько значений

У меня есть таблица (Table1), например:

Кол1 Кол2 Первый Код1, Код2, Код3 Второй Код2

Таким образом, Col2 может содержать несколько значений, разделенных запятыми, у меня есть другая таблица (Table2), которая содержит это:

КолА ColB Код1 Значение1 Код2 Vaue2 Код3 Значение3

Мне нужно создать представление, которое объединяет две таблицы (Table1 и Table2) и возвращает что-то вроде этого:

Кол1 Кол2 Первый Значение1, Значение2, Значение3 Второй Значение2

Это возможно? (Я использую Oracle DB, если это поможет.)

🤔 А знаете ли вы, что...
SQL может работать с множеством языковых расширений, таких как PL/SQL, T-SQL и другими.


84
2

Ответы:

Решено

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

Однако вы можете получить то, что хотите, используя оператор LIKE для поиска значений colA, которые являются подстроками столбца Col2. Добавьте разделители до и после, чтобы поймать первый и последний. Затем объедините резервные копии в единый список с помощью LISTAGG.

SELECT table1.col1,
       LISTAGG(table2.colB,',') WITHIN GROUP (ORDER BY table2.colB) value_list
  FROM table1,
       table2
 WHERE ','||table1.col2||',' LIKE '%,'||table2.colA||',%'
 GROUP BY table1.col1

Это не будет хорошо работать на больших объемах, потому что без эквивалентного соединения будут использоваться вложенные циклы, и вы не можете использовать индекс для предиката LIKE с % в начале. Сочетание вложенных циклов + FTS не нравится при больших объемах данных. Поэтому, если это ваша ситуация, вам нужно будет решить проблему 1NF, преобразовав таблицу1 в обычный реляционный формат, а затем соединить ее с таблицей2 с помощью соединения по эквивалентности, что позволит вместо этого использовать хеш-соединение. Так:

SELECT table1.col1,
       LISTAGG(table2.colB,',') WITHIN GROUP (ORDER BY table2.colB) value_list
  FROM (SELECT t.col1,
               SUBSTR(t.col2,INSTR(t.col2,',',1,seq)+1,INSTR(t.col2,',',1,seq+1)-(INSTR(t.col2,',',1,seq)+1)) col2_piece
          FROM (SELECT col1,
                       ','||col2||',' col2
                  FROM table1) t,
               (SELECT ROWNUM seq FROM dual CONNECT BY LEVEL < 10) x) table1,
       table2
 WHERE table1.col2_piece IS NOT NULL
   AND table1.col2_piece = table2.colA
 GROUP BY table1.col1

Если вам нужны значения в том же порядке в списке, что и термины, вы можете использовать:

SELECT t1.col1,
       LISTAGG(t2.colb, ',') WITHIN GROUP (
         ORDER BY INSTR(','||t1.col2||',', ','||t2.colA||',')
       ) AS value2
FROM   table1 t1
       INNER JOIN table2 t2
       ON INSTR(','||t1.col2||',', ','||t2.colA||',') > 0
GROUP BY 
       t1.col1

Что для примера данных:

CREATE TABLE Table1 (Col1, Col2) AS
SELECT 'First',  'Code1,Code2,Code3' FROM DUAL UNION ALL
SELECT 'Second', 'Code2' FROM DUAL;

CREATE TABLE Table2 (ColA, ColB) AS
SELECT 'Code1', 'XXXX' FROM DUAL UNION ALL
SELECT 'Code2', 'ZZZZ' FROM DUAL UNION ALL
SELECT 'Code3', 'YYYY' FROM DUAL;

Выходы:

COL1 VALUE2 Первый ХХХХ,ЗЗЗЗ,ГГГГ Второй ЗЗЗЗ

рабочий пример