将两个表与具有另一个表的多个列的列联接 [英] Join two tables with a column with multiple entries for the other table

问题描述

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

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>

这篇关于将两个表与具有另一个表的多个列的列联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！