34,587
社区成员
发帖
与我相关
我的任务
分享
--创建测试表
create table tb(id varchar(10))
--插入非空数据
insert tb select 'test'
go
--测试
select count(0) as 'count(0)',count(1) as 'count(1)',count(*) as 'count(*)',count(id) as 'count(id)' from tb
--结果
/*
count(0) count(1) count(*) count(id)
1 1 1 1
*/
--插入null值
insert tb values(null)
go
--测试
select count(0) as 'count(0)',count(1) as 'count(1)',count(*) as 'count(*)',count(id) as 'count(id)' from tb
--结果
/*
count(0) count(1) count(*) count(id)
2 2 2 1
*/
--插入空值
insert tb values ('')
go
--测试
select count(0) as 'count(0)',count(1) as 'count(1)',count(*) as 'count(*)',count(id) as 'count(id)' from tb
--结果
/*
count(0) count(1) count(*) count(id)
3 3 3 2
*/
--结论
/*
count(0)=count(1)=count(*) --不忽略null值和空值
count(列名) --忽略null值
*/
count(0)=count(1)=count(*) --不忽略null值和空值
count(列名) --忽略null值
create table #tmp (id int null)
insert #tmp
select 1
union all select 2
union all select 2
union all select 3
union all select 2
union all select null
union all select null
union all select 4
select count(*),count(id),count(all id),count(distinct id) from #tmp
----------- ----------- ----------- -----------
8 6 6 4
(所影响的行数为 1 行)