Oracle 空值计算总结

CREATE TABLE T1 (ID INT ,ID1 INT);INSERT INTO T1 VALUES(1,NULL);INSERT INTO T1 VALUES(NULL,NULL);INSERT INTO T1 VALUES(2,2);COMMIT;--实际上有三条记录,包括两个字段都为空的记录admin@ORCL> SELECT * FROM T1;        ID        ID1---------- ----------         1         NULL         2          2        NULL       NULLadmin@ORCL> SELECT COUNT(*) FROM T1;  COUNT(*)----------         3admin@ORCL> SELECT COUNT(ID) FROM T1; COUNT(ID)----------         2admin@ORCL> SELECT COUNT(ID1) FROM T1;COUNT(ID1)----------         1--空值的SUM测试admin@ORCL> SELECT SUM(ID) FROM T1;--SUM值不考虑空值   SUM(ID)----------         3admin@ORCL> SELECT AVG(ID) FROM T1;--AVG只除以了非为空的记录条数   AVG(ID)----------       1.5admin@ORCL> SELECT SUM(ID1) FROM T1;  SUM(ID1)----------         2         admin@ORCL> SELECT AVG(ID1) FROM T1;--avg只除以了非空的记录条数  AVG(ID1)----------         2--相加测试，NULL+NOT NULL VALUE = NULLadmin@ORCL> SELECT ID+ID1 FROM T1;    ID+ID1----------        NULL         4--MAX与MIN测试，最大值和最小值也不包含空admin@ORCL> select max(ID) from t1;   MAX(ID)----------         2admin@ORCL> select min(ID) from t1;   MIN(ID)----------         1--排序测试select * from t1 order by id;admin@ORCL> select * from t1 order by id;--默认是升序,可以看出NULL 默认最大        ID        ID1---------- ----------         1         NULL         2          2        NULL       NULLadmin@ORCL> select * from t1 order by id1;        ID        ID1---------- ----------         2          2        NULL       NULL         1         NULL--加上 NULLS first，将空值设为第一位admin@ORCL> select * from t1 order by id nulls first;        ID        ID1---------- ----------         1         2          2--distinct会包含nulladmin@ORCL> select distinct id from t1;        ID----------         1         2

?