现在有表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联合查询
有如下字段
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联合查询
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
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
from a,b
where a.action_type = 1 and a.action_id = b.id
union all
select a.id,a.action_type,a.action_id,c.name,c.honme
from a,c
where a.action_type = 2 and a.action_id = c.id
union all
select a.id,a.action_type,a.action_id,d.name,d.honme
from a,d
where a.action_type = 3 and a.action_id = d.id 难道是这样?
意思是有了,不过这样写法不好看。最好的办法是利用视图
譬如创建视图VW_ALL AS ...
把b,c,d union all起来,然后前面再加一列,例如:
select '1' action_type,xxx from b
union all
select '2' action_type,xxx from c
union all
select '3' action_type,xxx from d然后
select a.* from a join vw_all on vw_all.action_type=a.action_type and vw_all.id=a.id这样写法的唯一好处是代码比较好看,也容易理解,效果上则和分开的是一样的。