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 这条无法显示出来
解决方案 »
- 做过C#调用TimesTen的兄弟进来帮我看看这个问题
- oracle java.sql.SQLException: 列名无效 但在sql窗口可以通过,myEclipse 也不抛异常
- 关于Forms Builder 开发
- oracle数据容量是多少
- 求救,oracle数据库备份与恢复的问题!!!!!
- 在windows 2000 profession 安装oracle 10,点击开始安装即出错,各位帮帮忙!!!
- ★★讨论子查询★★
- 请问关于查询时索引的问题
- 数据增量问题,请指教!
- 关于查询的问题 急问!!!!
- ==上海著名外企: 招聘ERP工程师==
- 启动oracle出现了ORA-01033: ORACLE initialization or shutdown in progress问题
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';