select name from t2 where id in (select no from t1 where flag = 1)
union
select name from t3 where id in (select no from t1 where flag = 0)
union
select name from t3 where id in (select no from t1 where flag = 0)
2 where t1.flag=1 and t1.no=t2.id
3 union
4 select t1.no,t1.flag,t3.name from t1,t3
5 where t1.flag=0 and t1.no=t3.id;NO FLAG NAME
---------- ---------- ----------
001 1 机械
002 0 苹果
003 0 梨
004 1 汽车
从面向对象来看,T1是超类,T2和T3是派生的子类。Name是T2和T3共同的属性,却没有放到T1上去。因此才出现了本例所需要的SQL语句
-------------------------------------------------------
这种语句,只能通过循环取得。
yuhang823823(yuyu)所写的语句,在Oracle中应该将union 改成union all.因为name虽然有重名的,但不应该过滤掉。
多谢指点,我程序中使用的表比较复杂,我只是举个例子,
to zhaoyongzhu:
多谢,但该语句执行后name没有选出来,为什么?
select no, decode(flag,1,(select t2.name from t2
where t1.no=t2.id), (select t3.name from t3
where t1.no=t3.id))
from t1;
但应当保证在t2、t3里,每个代码唯一对应一个名称。
union all
select name from t3 where id in (select no from t1 where flag = 0)
同意mycode的观点。