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;