oracle sum为null时做法

当没记录时赋为0

 

select nvl(sum(area),0)area

 

 

 

以下部分为转载

 

 

本文发表于《软件报》2009年29期2009-07-20 第17版 属于本期推荐的文章之一

文章的难度不高，但是很有“科普”意义，有助于大家对NULL的认识。

 

在数据库中，空值用来表示实际值未知或无意义的情况。在数据结果中如果某行某列没有值，那么就称它为空值（NULL）。任何数据类型的列，如果没有使用非空（NOT NULL）或主键（PRIMARY KEY）来进行完整性限制，都可以用空值，在没有正确理解它的意义，忽视它存在的重要性，那和它之间发生的战争似乎就有点像“唐·吉诃德”一样了。



一、我们要理解NULL存在的意义


它和数据中的0值、字符串的空串所代表的意义是不一样的，它和任何数据之间的比较也没有意义。
在条件中引用到空值通常都使用 is Null或者is not null的方式来处理。
在一个允许使用空值的列，插入时没有明确指定数据值时，插入的就是空值。由于它的特殊意义，在实际使用时就尽量不要使用产生歧义表达式，如：
with t as(
select 10 id,null dat from dual
union all
select 20, 10 from dual )
select * from t  where dat in (10, null);
也许想查询出来dat值为10或者是Null的数据，其目的是枉然的，所以对于使用NULL时不要依赖于自己的想象，对于它需要写成这样：
Select * from t where dat in ( 10 ) or dat is
null;
由于NULL的存在，如果使用is null之类的条件通常索引也是不能起作用的。因而NULL的它显式地使用有助于别人的理解。



二、Oracle中处理NULL的函数


既然我们知道NULL值在处理中容易造成误解，在实际统计与查询分析中，要使用相应函数来进行数据处理，一方面保证结果的正确性，也让程序的可读性加强。对付NULL最好的武器有：Nvl、nvl2、Nullif，当然在某些情况下用decode也能很好地解决问题。下面来看看各路神仙处理NULL值时的特质吧。

NVL
用于对空值时返回指定值，否则返回原值。例如想把空值返回成当前系统时间：
Select nvl(xiugaisj, sysdate ) from t;
对于数据：
with tlist as (
select 10 as rin, NULL as rout from dual
union all
select NULL, 5  from dual
union all
select 7, 7 from dual
union all
select null, null from dual
union all
select 3, 5 from dual )
我们可以使用：
select sum( nvl(rin,0) - nvl(rout,0)) from tlist;
但由于sum函数具有忽略NULL值的计算功能，因此达成上述目标使用以下的SQL也是一样：
select sum(rin) - sum(rout) from tlist;

NVL2
用于对空值返回指定值，非空值返回另外一个值，它比较适用于那些统计空值数据个数时的用法，如果使用nvl2(rin,rin,0)那就是和nvl(rin,0)是一样的效果了。对于上述数据我们希望统计数据有效值的个数，就需要这样使用了：
Select sum(nvl2(rin,1,0)), sum(nvl2(rout,1,0)) from tlist;

NULLIF
其实用于比较两个数据之间的关系,如果相等则返回NULL,否则返回前一个值。比如：
Select nullif(rin, rout), rin, rout from tlist;
我们已经提及，与NULL之间的数据比较是没有意义的，所以上述情况的返回值还是挺有意思的：

001.png (2.14 K)

2009-7-23 9:47:19

 

从规则中都可以理解上述的数据值,1和5是数据不等,返回前面的值,3是相等返回空值,第2和4条由于自身值是NULL,所以无论如何返回都是空值。所以如果只是要达到这个效果，使用decode(rin,rout,null,rin)是一样的。但是NULLIF有一个特性，就是RIN不能是直接使用NULL，如果使用“Nullif(null,rin)”就会报相应的ORACLE错误，而使用：Decode(null,rout,null,rin)却是没有问题的。

在上面我们提及了处理NULL的函数，其实从10g开始，ORACLE还提供了很有意义的用于条件处理特殊的NULL相关函数，下面有请lnnvl上场：

LNNVL( condition )
的作用

Condition的结果	返回值
TRUE	FALSE
FALSE	TRUE
UNKNOWN	TRUE

    还是以实例来讲话比较好，对于结果：
with tlist as(
select 1000 id, 20 gty, NULL ilevel from dual union all
select 2000,15,8 from dual union all
select 2000,null,8 from dual union all
select 3000,8,10 from dual union all
select 4000,12,6 from dual union all
select 5000,2,2 from dual union all
select 6000,4,5 from dual)
select * from tlist where gty<ilevel;


在这样的情况下，如果gty或者是ilevel是空值的情况下，都不会满足条件，有时候我们可能希望这样的情况下，空值也都能够反应出来，那么只要这样就搞定：
Select * from tlist where lnnvl(gty>=ilevel);
在这里以下的语句和它等效：
Select * from tlist where gty<ilevel or gty is NULL or ilevel is null;
但有时候我们的后续的条件可能要复杂一些时，那么利用lnnvl就可显威力了。



三、NULL的排序


除了数据处理,其实在数据的返回时，NULL值的顺序也有很重要。我们关注数据的时候更多一些，所以无论是正向排序还是反向排序时，都希望把此列NULL值的数据排在最后，所以就要使用NULLS FIRST或者是NULLS LAST来处理，象空值在ORACLE的默认情况下都是当无穷大来处理的，所以我们希望按ilevel进行降序排列时，NULL值的数据排到后面就可以这样写：
Select * from tlist order
by ilevel desc
nulls
last;
当然有兴趣的话也可以看看nulls first的作用了。