一道关于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>

可以看到count(sal)和avg(sal)都是直接把字段为NULL的行排除了,答对了吗? :-)