标题: SQL Server2000聚合函数和空值
作者: dnawo
日期: 2010-04-10 06:26 PM
分类: 服务器
内容: SQL Server2000的聚合函数大都会忽略空值，所以在含有空值的列上使用聚合函数时需格外谨慎。例如有一个Student表如下：
我们用下边SQL语句统计下人数、平均年龄、最大年龄、最小年龄：
复制内容到剪贴板 程序代码select
count(*) as count1,
count(age) as count2,
avg(age) as [avg],
max(age) as [max],
min(age) as [min]
from Student
引用内容count1 count2 avg max min
----------- ----------- ----------- ----------- -----------
3 2 21 22 20
可以看到，除了count(*)，其他聚合函数都忽略了stu3。可以使用isnull函数给空值设置一个默认值，让聚合函数不忽略该行：
复制内容到剪贴板 程序代码select
count(*) as count1,
count(isnull(age,0)) as count2,
avg(age) as [avg],
max(age) as [max],
min(age) as [min]
from Student
引用内容count1 count2 avg max min
----------- ----------- ----------- ----------- -----------
3 3 21 22 20
注意：对avg、max、min聚合函数不应使用isnull，否则会出现用两个人的年龄计算三个人的平均年龄，这显然是不合理的。
很多时候，我们都会给字段设置一个默认值，当字段值为空值时就使用默认值，再看Student表：
我们用下边SQL语句统计下人数、平均年龄、最大年龄、最小年龄：
复制内容到剪贴板 程序代码select
count(*) as count1,
count(age) as count2,
avg(age) as [avg],
max(age) as [max],
min(age) as [min]
from Student
引用内容count1 count2 avg max min
----------- ----------- ----------- ----------- -----------
3 3 14 22 0
很显然，avg、min的值不是我们想要的，avg和min都应忽略stu3，这时我们可以用nullif函数让聚合函数忽略它：
复制内容到剪贴板 程序代码select
count(*) as count1,
count(nullif(age,0)) as count2,
avg(nullif(age,0)) as [avg],
max(nullif(age,0)) as [max],
min(nullif(age,0)) as [min]
from Student
引用内容count1 count2 avg max min
----------- ----------- ----------- ----------- -----------
3 2 21 22 20
说明：当以文本显示查询时，若对含空值的列使用聚合函数，SQL查询分析器会发出警告。
引用内容count1 count2 avg max min
----------- ----------- ----------- ----------- -----------
3 2 21 22 20
（所影响的行数为 1 行）
警告: 聚合或其它 SET 操作消除了空值。
+ 查看当前日志评论 (0)
────────────
PJBlog3 v3.2.9.518 Inside.
Processed In 0.031 ms
你没有权限发表评论
────────────
PJBlog3 v3.2.9.518 Inside.
Processed In 0.031 ms
暂无评论
────────────
PJBlog3 v3.2.9.518 Inside.
Processed In 0.031 ms