将两个表与具有另一个表的多个列的列联接

本文介绍了将两个表与具有另一个表的多个列的列联接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下问题. 我想加入两个表.

I have the following problem. I want to join two tables.

第一个表具有如下条目:

The first table has entries like the following:

T1 PK Info 1 one 2 two 3 three

第二个表是这样构建的:

The second table is build like this:

T2 PK FKT1 1 1,3 2 1,2,3 3 2

我的结果应显示以下内容

My Result should show the following

PK2 FKT1 InfoT1 1 1,3 One,Three 2 1,2,3 One,two,Three 3 2 Two

我只是不知道如何解决这个问题.

I just cant get an idea how to solve this.

仅使用sql selects可能吗?还是需要一个函数?

Is this possible only using sql selects or is a function needed?

亲切的问候

推荐答案

这并不困难，但是-如您所知，您宁愿不这样做.

It's not that difficult, but - as you were told, you'd rather NOT do that.

SQL> with 2 t1 (pk, info) as 3 (select 1, 'one' from dual union 4 select 2, 'two' from dual union 5 select 3, 'three' from dual 6 ), 7 t2 (pk, fkt1) as 8 (select 1, '1,3' from dual union 9 select 2, '1,2,3' from dual union 10 select 3, '2' from dual 11 ), 12 t2rows as 13 (select pk, regexp_substr(fkt1, '[^,]+', 1, column_value) fkt1, column_value rn 14 from t2, 15 table(cast(multiset(select level from dual 16 connect by level <= regexp_count(fkt1, ',') + 1 17 ) as sys.odcinumberlist)) 18 ) 19 select t2r.pk, 20 listagg(t2r.fkt1, ',') within group (order by t2r.rn) fkt1, 21 listagg(t1.info, ',') within group (order by t2r.rn) infot1 22 from t2rows t2r join t1 on t2r.fkt1 = t1.pk 23 group by t2r.pk 24 order by t2r.pk; PK FKT1 INFOT1 ---------- -------------------- -------------------- 1 1,3 one,three 2 1,2,3 one,two,three 3 2 two SQL>