SQL count命令，avg命令

with student as(
select 1 as id from dual union all
select null as id from dual union all
select 3 as id from dual 
)

--如果使用count(1)则计算所有，如果列出的使用count(列名)则会滤掉null值
select count(id), count(1),count(*) from student

 

 

with student as(
select 1 as id from dual union all
select null as id from dual union all
select 3 as id from dual 
)
select min(id), max(id) from student;

 

with student as(
select 1 as id from dual union all
select null as id from dual union all
select 3 as id from dual union all
select 3 as id from dual 
)
select count(distinct id) from student;

 

这里在count中看到的是【去掉null 以后，非重复的内容】

 

 

-------------------------------------------------------------------------------------------------

with student as(
select 1 as id from dual union all
select null as id from dual union all
select 3 as id from dual union all
select 3 as id from dual 
)
select avg(id) from student;

 

结果是7/3的结果，即滤掉NULL以后，使用(1+3+3)/3作为AVG的运算方式

但换个方法

with student as(
select 1 as id from dual union all
select null as id from dual union all
select 3 as id from dual union all
select 3 as id from dual 
)
select avg(nvl(id,0)) from student;

 

就是以4为除数了。

 

 那么，对于【组函数】我们可以使用下面这一个语句

 

with student as(
select 1 as id from dual union all
select null as id from dual union all
select 3 as id from dual union all
select 3 as id from dual 
)
select min(id),max(id),avg(nvl(id,0)),count(1) from student;