原帖由 newkid 于 2011-5-4 21:55 发表
t1的prodid拆成多行再连接t2然后再合并(LISTAGG, STRAGG, CONNECT BY等方法)
create table scott.t1(prodid varchar2(30));
insert into scott.t1 (PRODID) values ('0');
insert into scott.t1 (PRODID) values ('0,1,2,3,4,5,6');
insert into scott.t1 (PRODID) values ('1,3,4');
insert into scott.t1 (PRODID) values ('2,4,6');
insert into scott.t1 (PRODID) values ('1');
insert into scott.t1 (PRODID) values ('*');
create table scott.t2(idname varchar2(30));
insert into scott.t2 (IDNAME) values ('0|a');
insert into scott.t2 (IDNAME) values ('1|b');
insert into scott.t2 (IDNAME) values ('2|c');
insert into scott.t2 (IDNAME) values ('3|d');
insert into scott.t2 (IDNAME) values ('4|e');
insert into scott.t2 (IDNAME) values ('5|f');
insert into scott.t2 (IDNAME) values ('6|g');
select * from scott.t1;
PRODID
------------------------------
0
0,1,2,3,4,5,6
1,3,4
2,4,6
1
*
select * from scott.t2;
IDNAME
-------------
0|a
1|b
2|c
3|d
4|e
5|f
6|g
select nvl(listagg(decode(id,'*',null,id),',') within group (order by id),'*')||'|'||
listagg(regexp_substr(idname,'[^|]+',1,2),',') within group (order by id) agg from
(select t1.rowid rid,regexp_substr(prodid,'[^,]+',1,rn) id
from scott.t1,(select rownum rn from dual connect by rownum <= 10)) t1,scott.t2
where t1.id = regexp_substr(t2.idname,'[^|]+',1,1) or t1.id = '*'
group by rid;
AGG
----------------------------
0|a
0,1,2,3,4,5,6|a,b,c,d,e,f,g
1,3,4|b,d,e
2,4,6|c,e,g
1|b
*|a,b,c,d,e,f,g
listagg比较方便，10g的话还是得用connect by ，wm_concat虽然也可以，但排序比较麻烦，得用分析函数子句 |
