日期:2014-05-18  浏览次数:20560 次

请教一条简单的单表复合分组聚合查询语句
SQL code
表:
A          B
0          0
1          1
2          1
0          1
3          1
0          0
2          0


统计结果为:
A     B为0的数量     总数
0     2              3
1     0              1
2     1              2
3     0              1


------解决方案--------------------
SQL code

create table tb
(
  A int,
  B int
)

insert into tb 
select 0,0 union all 
select 1,1 union all 
select 2,1 union all 
select 0,1 union all 
select 3,1 union all 
select 0,0 union all 
select 2,0 


select A,sum(case when B=0 then 1 else 0 end) B为0的数量,COUNT(B) 总数 from tb group by A

------解决方案--------------------
SQL code

--> 测试数据:[A1]
if object_id('[A1]') is not null drop table [A1]
create table [A1]([A] int,[B] int)
insert [A1]
select 0,0 union all
select 1,1 union all
select 2,1 union all
select 0,1 union all
select 3,1 union all
select 0,0 union all
select 2,0

SELECT A,SUM(CASE B WHEN 0 THEN 1 ELSE 0 END) AS [B为0的次数],
COUNT(1) AS [B的总次数]
from A1 GROUP BY A
--你的语句修改后:
select A,sum(case when B =0 then 1 else 0 end) B为0的次数,
sum(case when B =0 OR B=1 then 1 else 0 end) 总次数 
from A1 group by A
/*
A    B为0的次数    B的总次数
0    2    3
1    0    1
2    1    2
3    0    1
*/