SELECT [Name], COUNT([Num]) AS [Name] FROM TableName GROUP BY [Name]
结果如下:
Name Num
aaa 20
bbb 10
上面的SQL语句查询只能统计出Num大于0,
我要把Num的为0的记录也统计出来,我需要的显示效果:
Name Num
aaa 20
bbb 10
ccc 0
SQL语句怎么该?
结果如下:
Name Num
aaa 20
bbb 10
上面的SQL语句查询只能统计出Num大于0,
我要把Num的为0的记录也统计出来,我需要的显示效果:
Name Num
aaa 20
bbb 10
ccc 0
SQL语句怎么该?
PS:COUNT([Num]) AS [Name] 应改成 COUNT(*) AS [Num]
SELECT [Name], COUNT(*) AS [Num] FROM TableName GROUP BY [Name]原来显示的结果:
Name Num
aaa 20
bbb 10
ccc null
显示的结果:
Name Num
………………
ccc NULL
或
Name Num
………………
ccc 0
insert @a
select 'aaa', 20 union all
select 'bbb', 10 union all
select 'ccc',''select name,isnull(num,0) from @a(所影响的行数为 3 行)name
----- -----------
aaa 20
bbb 10
ccc 0(所影响的行数为 3 行)
或
SELECT [Name], isnull(COUNT([Num]),0) AS [Name] FROM TableName GROUP BY [Name]
结果中没有:
Name Num
………………
ccc NULL
或
Name Num
………………
ccc 0
先把它更新为0update table set [num]=0 where [num] is null
select count(*) as num from temptab where 1<>1
结果:
num
0
select count(*) as num from temptab where 1<>1 group by id
结果:
num
id列不为空!