楼主的typeid4与前面的1-3之间的关系如何?如果typeid4= typeid1 + typeid2 + typeid3,那么可以采用以下的SQL:with a as ( select 1 id, '1' typeid1, '2' typeid2, '3' typeid3, '1,2,3' typeid4 from dual ) , b as ( select '1' id, '项目1' name from dual union select '2' , '项目2' from dual union select '3' , '项目3' from dual ) select a.id, b1.name, b2.name, b3.name, b1.name||','||b2.name||','||b3.name from a,b b1,b b2,b b3 where a.typeid1 = b1.id and a.typeid2 = b2.id and a.typeid3 = b3.id;
汗,楼主还在等回复啊?你的要求很难满足,据我所知行列转换是没有什么效率的代码的。偶只能给你一个不效率的代码。with a as ( select 1 id, '1' typeid1, '2' typeid2, '3' typeid3, '1,2,3' typeid4 from dual ) , b as ( select '1' id, '项目1' name from dual union select '2' , '项目2' from dual union select '3' , '项目3' from dual ) select t.id,b1.name,b2.name,b3.name,wm_concat(b4.name) from (select a.id,a.typeid1,a.typeid2,a.typeid3,regexp_substr(a.typeid4,'[^,]+', 1, tr.lv) typesubid,tr.lv from a,(select level lv from dual connect by level < 10) TR) T, b b1,b b2,b b3,b b4 where typesubid is not null and t.typeid1 = b1.id and t.typeid2 = b2.id and t.typeid3 = b3.id and t.typesubid = b4.id group by t.id,b1.name,b2.name,b3.name ;
(
select 1 id, '1' typeid1, '2' typeid2, '3' typeid3, '1,2,3' typeid4 from dual
)
,
b as
(
select '1' id, '项目1' name from dual union
select '2' , '项目2' from dual union
select '3' , '项目3' from dual
)
select a.id, b1.name, b2.name, b3.name, b1.name||','||b2.name||','||b3.name from a,b b1,b b2,b b3 where a.typeid1 = b1.id and a.typeid2 = b2.id and a.typeid3 = b3.id;
(
select 1 id, '1' typeid1, '2' typeid2, '3' typeid3, '1,2,3' typeid4 from dual
)
,
b as
(
select '1' id, '项目1' name from dual union
select '2' , '项目2' from dual union
select '3' , '项目3' from dual
)
select t.id,b1.name,b2.name,b3.name,wm_concat(b4.name)
from
(select a.id,a.typeid1,a.typeid2,a.typeid3,regexp_substr(a.typeid4,'[^,]+', 1, tr.lv) typesubid,tr.lv from a,(select level lv from dual connect by level < 10) TR) T,
b b1,b b2,b b3,b b4
where typesubid is not null and
t.typeid1 = b1.id and
t.typeid2 = b2.id and
t.typeid3 = b3.id and
t.typesubid = b4.id
group by t.id,b1.name,b2.name,b3.name ;