数据列汇总问题请教
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