select a,b,sum(case when c=0 then 1 else 0 end) [0个数] from [Table] group by a,b
select a,b,count(*) as num from tb where c=0 group by a,b
select a,b,(select count(1) from tb where a=t.a and b=t.b and c=0)[0个数] from tb t
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([A] int,[B] int,[C] int) insert [TB] select 1,1,1 union all select 2,2,0 union all select 3,3,0 union all select 1,2,0 union all select 1,1,0select * from [TB] SELECT A,B,SUM(CASE WHEN c = 0 THEN 1 ELSE 0 END)AS [count_c] FROM dbo.TB GROUP BY A,B/* A B count_c ----------- ----------- ----------- 1 1 1 1 2 1 2 2 1 3 3 1(4 行受影响) */
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] go create table [TB]([A] int,[B] int,[C] int) insert [TB] select 1,1,1 union all select 2,2,0 union all select 3,3,0 union all select 1,2,0 union all select 1,1,0select a,b,sum(case c when 0 then 1 else 0 end) as [count_c] from [TB] group by a,b /* a b count_c ----------- ----------- ----------- 1 1 1 1 2 1 2 2 1 3 3 1(4 行受影响)
from [Table]
group by a,b
from tb
where c=0
group by a,b
from tb t
if object_id('[TB]') is not null drop table [TB]
create table [TB]([A] int,[B] int,[C] int)
insert [TB]
select 1,1,1 union all
select 2,2,0 union all
select 3,3,0 union all
select 1,2,0 union all
select 1,1,0select * from [TB]
SELECT A,B,SUM(CASE WHEN c = 0 THEN 1 ELSE 0 END)AS [count_c]
FROM dbo.TB
GROUP BY A,B/*
A B count_c
----------- ----------- -----------
1 1 1
1 2 1
2 2 1
3 3 1(4 行受影响)
*/
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([A] int,[B] int,[C] int)
insert [TB]
select 1,1,1 union all
select 2,2,0 union all
select 3,3,0 union all
select 1,2,0 union all
select 1,1,0select a,b,sum(case c when 0 then 1 else 0 end) as [count_c] from [TB]
group by a,b
/*
a b count_c
----------- ----------- -----------
1 1 1
1 2 1
2 2 1
3 3 1(4 行受影响)