oracle 表查询两个字段相加_Oracle中某个字段里的多个值去匹配另外一张表的多个值...

前提：有两张表

表1：test

字段：a_urs_id,a

表1

​​​

表2：test1

字段：urs_id,urs_name​

表2

现在我想知道表一中a_urs_id字段对应表二那些urs_name,也就是想得到如下的结果：

结果

​解决方案：

with split_data as (

select a,key, rn, substr(str,

instr(str, ',', 1, rn)+1,

instr(str, ',', 1, rn+1) - instr(str, ',', 1,

rn) - 1) str

from

(select a,a_urs_id key, ','||a_urs_id||',' str from test)

a,

(select rownum rn from dual

connect by rownum < 10) b

where instr(str, ',', 1, rn+1)

> 0 )

select

a,key, substr(max(sys_connect_by_path(urs_name, ',')), 2)

a_name

from split_data a, test1

b

where a.str =

b.urs_id

start with rn = 1

connect

by key = prior key and rn-1 = prior rn

group

by a,key;

​

​