77家的会客2010

SQL中sum(),avg()等统计结果为null的解决方法
Weather:多云转晴,北风4-5级转东北风4-5级,(6~16)℃

在SQL语句中使用集合函数count(),返回结果肯定为非负整数,这也是用来判断数据有无的一个很有效的方法;

然后在用sum()进行集合相加的时候,如果sum()的对象没有记录,那么不会返回0,而返回null,这么做,不会影响数据库,因为数值型字段也是可以存放NULL的,因为在执行这样的SQL语句的时候,没有记录也不会报错。

但是执行完之后,要使用这个数据就有问题了。

1、在程序里处理的时候,比如进行四则运算或其他的强类型转换的时候,就会提供类型不正确或是与其它数据相加得到的结果变成NULL,因为NULL+number= Null。

2、统计的结果的SQL语句中有其它的运算,同1的最后一种情况,Null+ number = Null。就会把其它的有效数据也抹杀掉了。

解决方法:

1、使用isnull()方法进行判断,如isnull(sum(cnt),0),其中0为默认值,如果sum(cnt)的结果为null,则赋值0;同理此方法可用于某字段,isnull(cnt,0),这样就可以排除一些值为null的数值型字段了。而如果这样isnull(sum(isnull(cnt,0)),0),这样就可保万无一失了。

2、使用case when else流程控制语句,如可以用"case when sum(cnt) is null then 0 else sum(cnt) end"来代替sum(cnt),其中0同上一条的意义相同。

另外求平均函数avg() 也是一样的情况,相同的解决办法。

历史上的今天: [2009/04/11]春暖花开了
[2009/04/11]Python中的正则表达式入门(1)
[2007/04/11]豆浆为什么配油条

[SQL中sum(),avg()等统计结果为null的解决方法]的回复

littlenew 于 2008-05-12 14:24:43 发表 | IP:218.18.131.*

isnull(sum(cnt),0)还有这个用法?????

看来我又要长见识了。

柠檬园主 于 2008-05-15 09:23:50 发表 | IP:221.201.150.*

嗯,SQL的语法也是很庞大的。

littlenew 于 2008-08-06 14:35:29 发表 | IP:218.18.128.*

为什么我这样写却在我的access上出现了错误:用于函数参数的个数不对 在查询表达式 'isnull(sum(hits),0)' 中。

会不会你的这个是sql 2005上的东西呀。

4#   柠檬园主 于 2008-08-06 19:06:03 发表 | IP:221.201.145.*

这个是MS SQL和SYBASE上用的,AC没试过,不过根据出错信息,应该是有这个函数,只是参数不正确.

5#   littlenew 于 2008-08-21 11:06:43 发表 | IP:218.17.61.*

在access上是不能用isnull(sum(id),0),在sql 2K上是可以的。哈哈。

但这个null却不会发生在count的上面,只会返回0.

 

6#   柠檬园主 于 2008-08-21 14:48:47 发表 | IP:221.201.154.*

是的,COUNT()不需要,因为它统计的是记录的条数,不会出现NULL。

另外在DB2里,似乎不是isnull()而是ifnull()

Post a Comment~