Oracle COUNT(字段)与AVG(字段)不计算NULL值
一道关于AVG函数和NULL值的面试题
某部门有10个员工,其中9个工资为1000,另一个为NULL,当使用AVG函数取该部门平均工资时,结果应该是多少?
A.1000
B.900
我这里把题目再延伸一下,如果我们count(工资)结果是多少? A.10, B.9
实践出真知
SQL> select * from v$version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
SQL>
SQL> drop table t purge;
Table dropped.
SQL> create table t(sal number);
Table created.
SQL> insert into t values (10);
1 row created.
SQL> insert into t values ('');
1 row created.
SQL> insert into t values(0);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
SAL
----------
10
0
SQL> desc t
Name Null? Type
----------------------------------------- -------- -------------------------
SAL NUMBER
SQL> select count(*),count(sal),sum(sal),avg(sal) from t;
COUNT(*) COUNT(SAL) SUM(SAL) AVG(SAL)
---------- ---------- ---------- ----------
3 2 10 5
SQL>
目录 返回
首页