ORACLE两个表操作

[WYPGOOD]

问题如下：
table_1：数据如下：
table1_ID CONTENT
1 U1:10;U2:11;U3:01
2 U1:05;U2:11;U3:01
table_2：数据如下：
table2_ID KEYNAME KEYVALUE
1 U1:10 1号
2 U2:11 2号正常
3 U3:01 3号正常
4 U1:05 1号异常

需要结合两表实现：
table1_ID CONTENT
1 U1:1号;U2:2号正常;U3: 3号正常
2 U1:1号异常;U2:2号正常;U3: 3号正常

如何实现？

[weixin_30717569]

懂你意思 但是我是初学者 同等高手解题

[卖水果的net]

SQL> 
SQL> create table t1(t1_id int , content varchar(30));
Table created
SQL> insert into t1 values(1,'U1:10;U2:11;U3:01');
1 row inserted
SQL> insert into t1 values(2,'U1:05;U2:11;U3:01');
1 row inserted
SQL> commit ;
Commit complete
SQL> create table t2(t2_id int , keyname varchar(10) , keyvalue varchar(10));
Table created
SQL> insert into t2 values(1,'U1:10','1号');
1 row inserted
SQL> insert into t2 values(2,'U2:11','2号正常');
1 row inserted
SQL> insert into t2 values(3,'U3:01','3号正常');
1 row inserted
SQL> insert into t2 values(4,'U1:05','1号异常');
1 row inserted
SQL> commit ;
Commit complete
SQL> with m as (
  2  select t1.t1_id , t2.keyvalue from t1 , t2
  3  where ';' || t1.content || ';' like '%;' || t2.keyname || ';%'
  4  order by 1,instr(';' || t1.content || ';',';' || t2.keyname || ';')
  5  )
  6  select t1_id , wm_concat(keyvalue) content from m
  7  group by t1_id ;
                                  T1_ID CONTENT
--------------------------------------- --------------------------------------------------------------------------------
                                      1 1号,3号正常,2号正常
                                      2 1号异常,3号正常,2号正常
SQL> drop table t1 purge ;
Table dropped
SQL> drop table t2 purge ;
Table dropped

SQL> 

[卖水果的net]

如果你是 11g 以上的库，建议把 wm_concat 换成 listagg。

[蓝色蛋饼]

用replace貌似可以

with table_1 as
 (select 1 table1_id, 'U1:10;U2:11;U3:01' CONTENT  from dual
  union all
  select 2, 'U1:05;U2:11;U3:01' CONTENT from dual),
table_2 as
 (select 1 table2_ID, 'U1:10' KEYNAME, '1号' keyvalue from dual
  union all
  select 2, 'U2:11', '2号正常' from dual
  union all 
  select 3, 'U3:01', '3号正常' from dual
  union all
  select 4, 'U1:05', '1号异常' from dual),
c as
 (select a.table1_id,
         substr(b.keyname, 1, 3) ||
         replace(b.KEYNAME, b.KEYNAME, b.keyvalue) b4
    from table_1 a, table_2 b
   where instr(a.CONTENT, b.KEYNAME) > 0)
select c.table1_id, listagg(b4) within group (order by b4) a2 from c group by c.table1_id;