У меня есть таблица (Table1), например:
Таким образом, Col2 может содержать несколько значений, разделенных запятыми, у меня есть другая таблица (Table2), которая содержит это:
Мне нужно создать представление, которое объединяет две таблицы (Table1 и Table2) и возвращает что-то вроде этого:
Это возможно? (Я использую Oracle DB, если это поможет.)
🤔 А знаете ли вы, что...
SQL может работать с множеством языковых расширений, таких как PL/SQL, T-SQL и другими.
Наличие списка в таком столбце является нарушением первой нормальной формы. Это вызывает много трудностей в реляционной базе данных, подобной той, с которой вы сталкиваетесь сейчас.
Однако вы можете получить то, что хотите, используя оператор 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;
Выходы: