使用Count时需要注意的情况
With tab As
(
Select Null a From dual
Union All
Select Null a From dual
Union All
Select Null a From dual
)
Select Count(a),Count(1),Count(*) From tab;
(
Select Null a From dual
Union All
Select Null a From dual
Union All
Select Null a From dual
)
Select Count(a),Count(1),Count(*) From tab;
测试结果为:
COUNT(A) COUNT(1) COUNT(*)
---------- ---------- ----------
0 3 3
可见:
① 一般情况下,Count函数对于Null值是不作统计的;
② COUNT(*)等同于COUNT(1) ,它会对Null值作统计。
所以,在使用Group By时,尽量使用
Select count(1) From tab t Group By a
而不要使用
Select count(a) From tab t Group By a
否则对于Null值的统计结果将会为0