Oracle中这两种使用nvl函数的方法有什么区别(50)

update t_material_pbstocks pb set (CURINITMONEYAVERAGE)=( select nvl(t.CURENDMONEYAVERAGE,0) from t_material_pbstocks t where t.periodofbursar = '200812' and t.storehouseguid=pb.storehouseguid) where pb.periodofbursar = '200901'update t_material_pbstocks pb set (CURINITMONEYAVERAGE)=nvl(( select t.CURENDMONEYAVERAGE from t_material_pbstocks t where t.periodofbursar = '200812' and t.storehouseguid=pb.storehouseguid),0) where pb.periodofbursar = '200901'现在值等于 200812 的比 等于 200901 的区配记录少, nvl用的地方不同方法一没有区配的记录没有记录为0, 方法二得到正确结果，但为什么？以及当多个字段时应该怎么写?

其实很好理解的，我给你分解一下方法一 select nvl(t.CURENDMONEYAVERAGE,0) from t_material_pbstocks t where t.periodofbursar = '200812' and t.storehouseguid=pb.storehouseguid 当没有满足的记录时查询出来的结果为NULL方法二相当于select nvl( select nvl(t.CURENDMONEYAVERAGE,0) from t_material_pbstocks t where t.periodofbursar = '200812' and t.storehouseguid=pb.storehouseguid) ,0) from dual 当没有满足的记录时查询出来的结果为0多个字段更新就加逗号update a set (a.column1,a.column2,..a.columni)=(select b.column1,b.column2,..b.columni from bwhere a.column1=b.a.column1 and ....)where .....

多个字段时，如何用nvl

Update ASet (f0,f1,f2) = ( Select nvl(x0,0), nvl(x1,0), nvl(x2,0) from ( Select max(x0) X0, max(X1) x1, max(X2) X2, Count(*) C From B Where 条件2 ) )where 条件1;注意，子查询中多了一个Count，这样即使子查询中没有符合条件的记录，查询的结果也有一条Count(*)=0的记录。

有点意思，还有没有别的方法有点不好看哦，还不如用union all 个0