一个sql动态查询的问题
现在有表A
有如下字段
ID ACTION_TYPE ACTION_ID
1000 1 11111
1001 2 22222
1002 3 33333
表B
ID name honme
11111 lvb nj
表c
ID names honme
11111 lvc nj
22222 lv1c nj2c
表D
ID names honme
11111 lvD nj1D
22222 lv12 nj2D
33333 lv12 nj3D
现在当表A里面的 ACTION_TYPE 为‘1’时，表A的ACTION_ID和表B的ID关联查询
当表A里面的 ACTION_TYPE 为'2’时，表A的ACTION_ID和表C的ID关联查询
当表A里面的 ACTION_TYPE 为'3'时，表A的ACTION_ID和表D的ID关联查询
这个怎么联合查出来呢？？
众神可有办法。。。。
sql联合查询
[解决办法]
create or replace procedure p_dynamic_sql(ptye number)
is
declare
c_cur my_cursor;
v_sql varchar2(4000);
begin
v_sql:="select a.id,a.action_type,a.action_id,b.name,b.home from table1 a,table2 b where a.id=b.id";
if ptype =2 then
v_sql:=replace(v_sql,"table3");
end if;
if ptype=3 then
v_sql:=replace(v_sql,"table4");
end if;
Open c_cur for v_sql;
end p_dynamic_sql;
/
[解决办法]
这样可以通过么
select ID,ACTION_TYPE,
decode(ACTION_TYPE,'1',(select name from b where b.id = a.id),
'2',(select name from c where c.id = a.id),
'3',(select name from d where d.id = a.id),'')
from a
select a.*,
coalesce(b.name, c.name, d.name),
coalesce(b.honme, c.honme, d.honme)
from a
left join b
on a.action_type = '1'
and a.action_id = b.id
left join c
on a.action_type = '2'
and a.action_id = c.id
left join d
on a.action_type = '3'
and a.action_id = d.id;
select ID,ACTION_TYPE,
decode(ACTION_TYPE,'1',(select name from b where b.id = a.action_id),
'2',(select name from c where c.id = a.action_id),
'3',(select name from d where d.id = a.action_id),'')
from a
