2张(A,B)表进行连接查询 如果B表里面没有数据就用“0”来代替 这个有办法实现没有
A aid aname B bid aid bname byear
1 asdf 1 1 tt 2007
2 tttt 2 1 tt 2008 select aid,aname, byear where byear='2007'我要让A表中的数据2也显示 ,因为在B表中没有,所有要用'0'来显示 这样的效果要怎么写SQL结果数据为:
1 asdf 2007
2 tttt 0select aid,aname,nvl(byear,0) from a left join b on a.aid=b.bid and byear='2007' 这样的话 tttt 这条无法显示出来
A aid aname B bid aid bname byear
1 asdf 1 1 tt 2007
2 tttt 2 1 tt 2008 select aid,aname, byear where byear='2007'我要让A表中的数据2也显示 ,因为在B表中没有,所有要用'0'来显示 这样的效果要怎么写SQL结果数据为:
1 asdf 2007
2 tttt 0select aid,aname,nvl(byear,0) from a left join b on a.aid=b.bid and byear='2007' 这样的话 tttt 这条无法显示出来
SQL> SELECT A.AID,
2 A.ANAME,
3 NVL(BYEAR,'0') "YEAR"
4 FROM A,
5 B
6 WHERE A.AID = B.AID(+)
7 AND NVL(B.BYEAR,'2007') = '2007'; AID ANAME YEAR
---------- ----- ----
1 ASDF 2007
2 TTTT 0SQL>
select aid, aname, decode(byear,'2007',byear,0)
from a
left join b on a.aid = b.bid
;
from a
left join b on a.aid = b.bid
;
from a, b
where a.aid = b.bid(+)
and byear(+) = '2007'
(select a.aid,a.aname,b.byear from a left join b on a.aid=b.bid)
from (select a.aid, a.aname, b.byear from a left join b on a.aid = b.bid)
2 A.ANAME,
3 NVL(BYEAR,'0') "YEAR"
4 FROM A,
5 B
6 WHERE A.AID = B.AID(+)
7 AND B.BYEAR(+) = '2007';