select a.*,case when exists(select 1 from son b where b.fatherName=a.fatherName) then 'Yes' else 'No' end haveSon from father a--1 大明 Yes --2 大狗 No --3 大傻 Yes --4 大林 Yes
用下外连接 select distinct a.id,a.fathernae,deocode(b.fathername,null,'no','yes') from father a,son b where b.fathername(+)=a.fathername
with father as( select 1 id,'大明' fathername from dual union all select 2 id,'大狗' fathername from dual union all select 3 id,'大傻' fathername from dual union all select 4 id,'大林' fathername from dual ),son as( select '小明' sonname,'大明' fathername from dual union all select '小傻A' sonname,'大傻' fathername from dual union all select '小傻B' sonname,'大傻' fathername from dual union all select '小林' sonname,'大林' fathername from dual ) select id,f.fathername,s.sonname,decode(sonname,null,'no','yes') haveson from father f,son s where f.fathername = s.fathername(+) order by id
用case when 这个不错,绝对sql高手啊,我也是最近才知道还有这个功能的呵呵
select distinct a.id, a.fathername, decode(b.fathername, null, 'no', 'yes') from father a left outer join son b on b.fathername = a.fathername
select a.*,case when exists(select 1 from son b where b.fatherName=a.fatherName) then 'Yes' else 'No' end haveSon
from father a--1 大明 Yes
--2 大狗 No
--3 大傻 Yes
--4 大林 Yes
select distinct a.id,a.fathernae,deocode(b.fathername,null,'no','yes')
from father a,son b
where b.fathername(+)=a.fathername
select 1 id,'大明' fathername from dual
union all
select 2 id,'大狗' fathername from dual
union all
select 3 id,'大傻' fathername from dual
union all
select 4 id,'大林' fathername from dual
),son as(
select '小明' sonname,'大明' fathername from dual
union all
select '小傻A' sonname,'大傻' fathername from dual
union all
select '小傻B' sonname,'大傻' fathername from dual
union all
select '小林' sonname,'大林' fathername from dual
)
select id,f.fathername,s.sonname,decode(sonname,null,'no','yes') haveson
from father f,son s where f.fathername = s.fathername(+) order by id
用case when 这个不错,绝对sql高手啊,我也是最近才知道还有这个功能的呵呵
select distinct a.id, a.fathername, decode(b.fathername, null, 'no', 'yes')
from father a
left outer join son b on b.fathername = a.fathername