有一张表A,字段如下:
Name Result time
aa 1 2008-12-00
aa 2 2008-13-00
aa 1 2008-14-00
bb 1 2008-12-00
bb 255 2008-13-00
bb 2 2008-14-00
cc 2 2008-12-00
cc 255 2008-13-00
cc 3 2008-14-00
求:一段时间内某个名称(Name)的成功率=成功次数/总次数,其中1-代表成功, 其他代表失败;
结果如下: aa = 2 / 3
bb = 1 / 3
cc = 0 / 3[这个比较特殊,没有成功次数]
谢谢高手
Name Result time
aa 1 2008-12-00
aa 2 2008-13-00
aa 1 2008-14-00
bb 1 2008-12-00
bb 255 2008-13-00
bb 2 2008-14-00
cc 2 2008-12-00
cc 255 2008-13-00
cc 3 2008-14-00
求:一段时间内某个名称(Name)的成功率=成功次数/总次数,其中1-代表成功, 其他代表失败;
结果如下: aa = 2 / 3
bb = 1 / 3
cc = 0 / 3[这个比较特殊,没有成功次数]
谢谢高手
select a.Name,a.count/b.count
from
(select Name,count(Result) as 'count'
from A
where Result = 1
group by Name) a inner join
(select Name,count(Name) as 'count'
from A
group by Name) b on a.Name = b.Name
insert into #A
select 'aa', 1, '2008-12-00' union all
select 'aa', 2, '2008-13-00' union all
select 'aa' , 1, '2008-14-00' union all
select 'bb' , 1, '2008-12-00' union all
select 'bb' , 255, '2008-13-00' union all
select 'bb' , 2, '2008-14-00' union all
select 'cc' , 2, '2008-12-00' union all
select 'cc' , 255, '2008-13-00' union all
select 'cc' , 3, '2008-14-00'
go
select [name],(select count(*) from #A where result=1 and [name]=a.[name])*1.0/(select count(*) from #A where [name]=a.[name]) as rate from #A a group by [name]
/*
name rate
----- ---------------------------------------
aa 0.666666666666
bb 0.333333333333
cc 0.000000000000(3 行受影响)
*/