查询以加入 Oracle 表

代码大师

我有一个“主”表,如下所示:

Entity   Cat1         Cat2
A      Mary;Steve    Jacob
B      Alex;John     Sally;Andrew

另一个表“PERSON”具有人名(也可以是 InfoID)与电子邮件的关联。

Name   Email               InfoID
Mary   [email protected]      mryD
Steve  [email protected]     stvR
Jacob  [email protected]     jacbb
Sally  [email protected]     sallD
Alex   [email protected]      alexT
John   [email protected]      johP
Andrew [email protected]    andV

我想与 master 一起加入 person 表,例如:

Entity  Cat1          EmailCat1                          Cat2              EmailCat2
A       Mary;Steve    [email protected];[email protected]   Jacob             [email protected]
B       Alex;John     [email protected];[email protected]    Sally;Andrew      [email protected];[email protected]

关于如何去做的任何见解?

多域作战

老实说,您的主表设计需要标准化。但是,与此同时,您可以在下面尝试此查询:

with 
  needed_rows_for_cat1_tab (lvl) as (
  select level from dual 
    connect by level <= (select max(regexp_count(Cat1, ';')) from Your_bad_master_tab) + 1
)
, needed_rows_for_cat2_tab (lvl) as (
  select level from dual 
    connect by level <= (select max(regexp_count(Cat2, ';')) from Your_bad_master_tab) + 1
)
, split_cat1_val_tab as (
      select Entity, Cat1
        , substr(Cat1||';'
          , lag(pos, 1, 0)over(partition by Entity order by lvl) + 1
          , pos - lag(pos, 1, 0)over(partition by Entity order by lvl) - 1
          ) val
        , lvl
        , pos
        , 1 cat
      from (
      select Entity, Cat1, instr(Cat1||';', ';', 1, r1.lvl)pos, r1.lvl
      from Your_bad_master_tab c1
        join needed_rows_for_cat1_tab r1 on r1.lvl <= regexp_count(Cat1, ';') + 1
      )
)
, split_cat2_val_tab as (
      select Entity, Cat2
        , substr(Cat2||';'
          , lag(pos, 1, 0)over(partition by Entity order by lvl) + 1
          , pos - lag(pos, 1, 0)over(partition by Entity order by lvl) - 1
          ) val
        , lvl
        , pos
        , 2 cat
      from (
      select Entity, Cat2, instr(Cat2||';', ';', 1, r2.lvl)pos, r2.lvl
      from Your_bad_master_tab c1
        join needed_rows_for_cat2_tab r2 on r2.lvl <= regexp_count(Cat2, ';') + 1
      )
)
select ENTITY
  , max(decode(cat, 1, CAT1, null)) CAT1
  , listagg(decode(cat, 1, EMAIL, null), ';')within group (order by lvl) EmailCat1 
  , max(decode(cat, 2, CAT1, null)) CAT2
  , listagg(decode(cat, 2, EMAIL, null), ';')within group (order by lvl) EmailCat2 
from (
  select c.*, p.Email
  from split_cat1_val_tab c join Your_person_tab p on (c.val = p.name)
  union all
  select c.*, p.Email
  from split_cat2_val_tab c join Your_person_tab p on (c.val = p.name)
)
group by ENTITY
;

这是您的示例数据

--drop table Your_bad_master_tab purge;
create table Your_bad_master_tab (Entity, Cat1, Cat2) as
select 'A', 'Mary;Steve', 'Jacob' from dual union all
select 'B', 'Alex;John', 'Sally;Andrew' from dual
;

--drop table Your_person_tab purge;
create table Your_person_tab (Name, Email, InfoID) as               
select 'Mary',    '[email protected]'     ,'mryD'   from dual union all
select 'Steve',   '[email protected]'    ,'stvR'   from dual union all
select 'Jacob',   '[email protected]'    ,'jacbb'   from dual union all
select 'Sally',   '[email protected]'    ,'sallD'   from dual union all
select 'Alex',    '[email protected]'     ,'alexT'   from dual union all
select 'John',    '[email protected]'     ,'johP'   from dual union all
select 'Andrew',  '[email protected]'   ,'andV'   from dual
;

数据库<>小提琴

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章