SQL SERVER – Difference between COUNT(DISTINCT) vs COUNT(ALL)

http://blog.sqlauthority.com/2011/03/08/sql-server-difference-between-countdistinct-vs-countall/

COUNT(DISTINCT)和COUNT(ALL)的区别：前者像select distinct 字段1,字段2 from table一样会去掉重复的，后者则是统计字段的所有数据条数，与Count(字段1)相同。
COUNT(ALL)中的ALL实际上是缺省项，不需要指定。
(注：本人还有点怀疑COUNT(ALL)与COUNT(字段1)是否有区别，待验证)。

Here is the script:

1 SELECT COUNT ( [ Title ] ) Value
2 FROM [ AdventureWorks ] . [ Person ] . [ Contact ]
3 GO
4 SELECT COUNT ( ALL [ Title ] ) ALLValue
5 FROM [ AdventureWorks ] . [ Person ] . [ Contact ]
6 GO
7 SELECT COUNT ( DISTINCT [ Title ] ) DistinctValue
8 FROM [ AdventureWorks ] . [ Person ] . [ Contact ]
9 GO

注：select count(字段1) from table，当字段1若有为null的记录，则此记录不在count结果内，要注意。

1 SELECT COUNT ( * ) FROM table
2 WHERE Column1 IS NULL .
3
4 select sum (any_null_data_count) from
5 (
6 select case when [ any_null_data ] is null then 1 else 0 end as any_null_data_count FROM [ AdventureWorks ] . [ Person ] . [ Contact ] where [ any_null_data ] is null
7 ) h1

转载于:https://www.cnblogs.com/net205/archive/2011/03/09/1979214.html