数据列汇总问题请教
select   p.projid,p.projname,(select   sum(r.bidprice)   from   ibs_t_bid_node_hitbidresu   r   where   r.projid=   p.projid)as   amount   from   ibs_t_bid_proj_info   p
上面我是用的子查询   项目中标金额是用projid关联的另外一张表
我想问下不用子查询,用join   怎么写,还有如果bidprice列为空,默认给给O
象SQL   ISNULL(SUM(F_bidprice),0)这样   怎么写

俺是oracle新手   请教了


------解决方案--------------------
SQL> SELECT * FROM ibs_t_bid_proj_info;

PROJID PROJNAME
---------- ----------
001 AAA
002 BBB
003 CCC
004 DDD

SQL> SELECT * FROM ibs_t_bid_node_hitbidresu;

PROJID BIDPRICE
---------- ----------
001 100
001 200
002 300
002 400
003 100
003
003

已选择7行。

SQL> select p.projid,max(p.projname) projname,sum(nvl(r.bidprice,0)) bidprice
2 FROM ibs_t_bid_proj_info p,ibs_t_bid_node_hitbidresu r
3 where p.projid= r.projid(+) group by p.projid;

PROJID PROJNAME BIDPRICE
---------- ---------- ----------
001 AAA 300
002 BBB 700
003 CCC 100
004 DDD 0

SQL>
------解决方案--------------------

--MS SQL
select p.projid,p.projname,isnull(sum(r.bidprice), 0) As amount from ibs_t_bid_proj_info p left join ibs_t_bid_node_hitbidresu r on r.projid= p.projid
group by p.projid,p.projname

--ORACLE
try


select p.projid,p.projname,nvl(sum(r.bidprice), 0) As amount from ibs_t_bid_proj_info p left join ibs_t_bid_node_hitbidresu r on r.projid= p.projid
group by p.projid,p.projname