关于一个统计的算法问题：

T 表结构如下：

A B C D
1 2 3 1
4 5 6 1
1 2 2 1
2 1 2 2
1 2 3 2
1 1 1 2

需求:按D 分组统计 A=1,B=2,C=1 and B=2的个数！
下面是按照自己的算法统计出来的如图

 

 

--下面创建测试脚本

create table t(A int,B int ,C int ,D int)

---测试数据
while(1=1)
begin
insert into t
select * from
(
 select convert(int,rand()*1000) a ,
  convert(int,rand()*1000) b,
  convert(int,rand()*1000) c,
  convert(int,rand()*1000) d
) v
end

 

Title

--算法a-----------------------
declare @begin_date datetime
declare @end_date datetime
select @begin_date = getdate()

select tt.d,r1,r2,r3
from
(
 select distinct d from t
) tt
left join
(
 select d,count(*) r1 from t where a=1 group by d
) aa on tt.d=aa.d
left join
(
select d,count(*) r2 from t where b=2 group by d
) bb on tt.d=bb.d
left join
(
select d,count(*) r3 from t where c=1 and b=2 group by d
) cc on tt.d=cc.d


select @end_date = getdate()
select datediff(ms,@begin_date,@end_date) as '用时/毫秒'

---------------------------------------------------------

 

 

Title

--算法b -------------------------------------------------
declare @begin_date datetime
declare @end_date datetime
select @begin_date = getdate()

select d,sum(rr1) rr1,sum(rr2) rr2,sum(rr3) rr3
from
(
select d, case when a=1 then 1 else 0 end rr1,
case when b=2 then 1 else 0 end rr2,
case when c=1 and b=2 then 1 else 0 end rr3
from t
) tt
group by d

select @end_date = getdate()
select datediff(ms,@begin_date,@end_date) as '用时/毫秒'


 


 

关于算法的扩展，随着 统计的条件的增加，各个算法的优缺点，或者大家有什么更好的算法，大家来讨哈！

不好意思因为急，所以放首页了，大家多多支持下，谢谢！