我要执行如下的sql语句
select nvl(b.material_money,0)+nvl(c.ysk_money,0)-nvl(a.gathering_money,0) as money,
c.customer_id as customer_id from
(select nvl(sum(nvl(gathering_money,0)),0) as gathering_money,customer_id,seller_id from
cs_gathering where customer_id='000742' and seller_id='0002025051'
group by customer_id,seller_id) a,
(select nvl(sum(nvl(material_money,0)),0)
as material_money from cs_bill_of_lading where customer_id='000742'
and distill_man=(select seller from cs_seller where seller_id='0002025051')
group by customer_id,distill_man) b,
(select sum(nvl(ysk_money,0)) as ysk_money,customer_id,sell_id from customer_ysk
where customer_id='000742' and sell_id='0002025051' group by customer_id,sell_id) c
where c.sell_id=a.seller_id
and c.customer_id = a.customer_id
当其中某一张表中的customer_id='000742' and sell_id='0002025051'条件不成立时,就会没有返回值,只要当三张表都满足customer_id='000742' and sell_id='0002025051'条件时,才能得到我要的结果,请问如何做才能避免上面的问题
select nvl(b.material_money,0)+nvl(c.ysk_money,0)-nvl(a.gathering_money,0) as money,
c.customer_id as customer_id from
(select nvl(sum(nvl(gathering_money,0)),0) as gathering_money,customer_id,seller_id from
cs_gathering where customer_id='000742' and seller_id='0002025051'
group by customer_id,seller_id) a,
(select nvl(sum(nvl(material_money,0)),0)
as material_money from cs_bill_of_lading where customer_id='000742'
and distill_man=(select seller from cs_seller where seller_id='0002025051')
group by customer_id,distill_man) b,
(select sum(nvl(ysk_money,0)) as ysk_money,customer_id,sell_id from customer_ysk
where customer_id='000742' and sell_id='0002025051' group by customer_id,sell_id) c
where c.sell_id=a.seller_id
and c.customer_id = a.customer_id
当其中某一张表中的customer_id='000742' and sell_id='0002025051'条件不成立时,就会没有返回值,只要当三张表都满足customer_id='000742' and sell_id='0002025051'条件时,才能得到我要的结果,请问如何做才能避免上面的问题
解决方案 »
- Red hat 5.3下安装Oracle 10g出错,求解决!
- OCI库函数中OCIHandleAlloc(...)函数的问题
- Oracle如何禁止客户端以SYS用户登录
- 问高人在TC中如何调用oracle数据库中的数据
- 在触发器里可以知道影响行数马?
- 很多面试试题都有这样一道题,就是写个SQL语句,输出表中第M条数据到第N条数据,这是考什么啊?
- sqlserver表导入oracle时的问题?
- 急!sql语句问题!
- 急!急!急!各位大虾!请问在Oracle中如何定义外键组??
- Oracle系统预设的系统管理员都有什么不同!
- 如何创建表函数例如: select * from table(函数名(参1,参2)) 反回结果集
- oracle9.2.0.1能否支持NAT后的访问服务?
nvl(customer_id,'-1000')='000742'
nvl(customer_id,'-1000')='000742'
是什么意思
先看看哪个表里面没有这样的记录,如a,b,c三个这样的表,如果c中没有这样的记录,就把where条件改为 where a.id=c.id(+) and b.id=c.id(+)即可解决。
and c.customer_id(+) = a.customer_id
修改为以下:
nvl(sum(ysk_money),0)
把所有都修改为第二种形式吧