1 概述

聚合函数在计算时会排除 null，如：count()、sum()、max()、min()、avg()
但 count(列) 例外

2 示例

with student_info as (
  select 1 sno, 110 score from dual union all
  select 2 sno, null score from dual union all
  select 3 sno, 90 score from dual
)
select avg(si.score), -- (110 + 90) / 2 = 100
       avg(coalesce(si.score, 0)) -- (110 + 90) / 3 = 66.67
  from student_info si;
1
2
3
4
5
6
7
8

3 扩展

3.1 聚合函数排除 null

with student_info as (
  select 1 sno, 110 score from dual union all
  select 2 sno, null score from dual union all
  select 3 sno, 90 score from dual
)
select sum(si.score), -- 200
       max(si.score), -- 110
       min(si.score)  -- 90
  from student_info si;
1
2
3
4
5
6
7
8
9

3.2 count(1)、count(*)、count(列) 区别

• count(1)、count(*)：计算所有列的数量（含 空列 null）
• count(列)：计算非空列的数量

with student_info as (
  select 1 sno, 110 score from dual union all
  select 2 sno, null score from dual union all
  select 3 sno, 90 score from dual
)
select count(1), -- 3
       count(*), -- 3
       count(si.score) -- 2
  from student_info si;
1
2
3
4
5
6
7
8
9