请看以下代码
select max(a.存货编码) as 存货编码,max(a.存货名称) as 存货名称,max(a.存货规格描述) as 存货规格描述,max(a.客户型号) as 客户型号,max(a.订单号) as 订单号,
isnull(sum(a.数量),0) as 入库总数量, isnull(sum(b.数量),0) as 发货总数量,isnull(sum(a.数量),0)-isnull(sum(b.数量),0) as 库存数量
from 成品入库子表 a full join 发货单子表 b on a.订单号 = b.订单号 and a.AUTOID = b.AUTOID
group by a.订单号,b.订单号,a.AUTOID,b.AUTOID
因为 a表 和 b表 都有可能比对方要多,所以必须要用 full join,但实际上sum(a.数量),sum(b.数量)的结果是错误的,不知道为什么,
select max(a.存货编码) as 存货编码,max(a.存货名称) as 存货名称,max(a.存货规格描述) as 存货规格描述,max(a.客户型号) as 客户型号,max(a.订单号) as 订单号,
isnull(sum(a.数量),0) as 入库总数量, isnull(sum(b.数量),0) as 发货总数量,isnull(sum(a.数量),0)-isnull(sum(b.数量),0) as 库存数量
from 成品入库子表 a full join 发货单子表 b on a.订单号 = b.订单号 and a.AUTOID = b.AUTOID
group by a.订单号,b.订单号,a.AUTOID,b.AUTOID
因为 a表 和 b表 都有可能比对方要多,所以必须要用 full join,但实际上sum(a.数量),sum(b.数量)的结果是错误的,不知道为什么,
改为
sum(isnull(a.数量,0)) as 入库总数量
其他同.
所以要先将可能存在NULL的值转为0,再求和,而不是先求和再去检查是否为NULL.因为即使你查到是NULL,转为0了,但那只是最终结果,运算过程中的数据被NULL掉了.
这样,
isnull(sum(a.数量),0) as 入库总数量
与
sum(isnull(a.数量,0)) as 入库总数量
之间的差别,你就应该能看出来了.