count_sum_distinct與null

SQL> create table t_agg(a int,b int,c int);
 
Table created
 
SQL> insert into t_agg values(1,null,1);
 
1 row inserted
 
SQL> insert into t_agg values(1,2,null);
 
1 row inserted
 
SQL> insert into t_agg values(2,1,null);
 
1 row inserted
 
SQL> insert into t_agg values(2,null,null);
 
1 row inserted
 
SQL> insert into t_agg values(3,4,8);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from t_agg;
 
                                      A                                       B                                       C
--------------------------------------- --------------------------------------- ---------------------------------------
                                      1                                                                               1
                                      1                                       2
                                      2                                       1
                                      2                                        
                                      3                                       4                                       8
 
SQL> select count(b),count(c) from t_agg group by a;
 
  COUNT(B)   COUNT(C)
---------- ----------
         1          1
         1          0
         1          1
 
SQL> select a,count(b),count(c) from t_agg group by a;
 
                                      A   COUNT(B)   COUNT(C)
--------------------------------------- ---------- ----------
                                      1          1          1
                                      2          1          0
                                      3          1          1
 
SQL> truncate table t_agg;
 
Table truncated
 
SQL> insert into t_agg(a,b) values(1,null);
 
1 row inserted
 
SQL> insert into t_agg(a,b) values(1,1);
 
1 row inserted
 
SQL> insert into t_agg(a,b) values(1,1);
 
1 row inserted
 
SQL> insert into t_agg(a,b) values(2,1);
 
1 row inserted
 
SQL> insert into t_agg(a,b) values(2,2);
 
1 row inserted
 
SQL> insert into t_agg(a,b) values(3,1);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from t_agg;
 
                                      A                                       B                                       C
--------------------------------------- --------------------------------------- ---------------------------------------
                                      1                                        
                                      1                                       1
                                      1                                       1
                                      2                                       1
                                      2                                       2
                                      3                                       1
 
6 rows selected
 
SQL> select a,count(distinct b) from t_agg group by a;
 
                                      A COUNT(DISTINCTB)
--------------------------------------- ----------------
                                      1                1
                                      2                2
                                      3                1
 
SQL> truncate table t_agg;
 
Table truncated
 
SQL> insert into t_agg(a) values(null);
 
1 row inserted
 
SQL> insert into t_agg(a) values(null);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select count(a) from (select distinct a as a from t_agg);
 
  COUNT(A)
----------
         0

小結：count,sum,distinct忽略null,不計算在內