各位大侠，帮忙看看怎么使用存储过程实现下面的统计，数据量很大。
时间段 产量 合格数量 良率
2013-07-09 16:20 50 30 0.6
2013-07-09 16:30 40 35 0.86
2013-07-09 16:40 46 36 0.78
2013-07-09 16:50 42 37 0.88
2013-07-09 17:00 41 38 0.93
2013-07-09 17:10 60 42 0.7
2013-07-09 17:20 55 33 0.6
2013-07-09 17:30 52 34 0.65
2013-07-09 17:40 53 46 0.87
sql?2008?每十分钟统计良率
------解决方案--------------------
if object_id('tb') is not null drop table tb
go
create table tb(BarCode varchar(30),TestTime datetime,Result varchar(10))
insert into tb(BarCode,TestTime,Result)
select 'DYD3282AT93FD0785','2013-07-09 16:20','PASS' union all
select 'DYD3283AK6NFD0782','2013-07-09 16:21','PASS' union all
select 'DYD3283AK6TFD078X','2013-07-09 16:22','PASS' union all
select 'DYD3283AK6SFD078Y','2013-07-09 16:23','PASS' union all
select 'DYD3283AK6MFD0783','2013-07-09 16:23','PASS' union all
select 'DYD3283AK6QFD0780','2013-07-09 16:24','PASS' union all
select 'DYD3283AK6EFD078A','2013-07-09 16:24','PASS' union all
select 'DYD3283AK6FFD0789','2013-07-09 16:25','PASS' union all
select 'DYD3283AK6JFD0786','2013-07-09 16:26','PASS' union all
select 'DYD3283AK6KFD0785','2013-07-09 16:26','PASS' union all
select 'DYD3282AT91FD0787','2013-07-09 16:28','PASS' union all
select 'DYD3283BKEWFD0781','2013-07-09 16:28','PASS' union all
select 'DYD3282BTBSFD0787','2013-07-09 16:29','PASS' union all
select 'DYD3283AL1MFD078H','2013-07-09 16:30','PASS' union all
select 'DYD3282AU3AFD078F','2013-07-09 16:31','FAIL' union all
select 'DYD3283BK4HFD078A','2013-07-09 16:31','PASS' union all
select 'DYD3272AU8EFD078Z','2013-07-16 14:48','PASS' union all
select 'DYD3272AT8GFD078Y','2013-07-16 14:48','FAIL' union all
select 'DYD3272AT24FD078U','2013-07-16 14:49','PASS' union all
select 'DYD3272AT7RFD078S','2013-07-16 14:49','FAIL' union all
select 'DYD3272AT1XFD0784','2013-07-16 14:51','FAIL' union all
select 'DYD3272AT4MFD0785','2013-07-16 14:51','FAIL' union all
select 'DYD3272AT4QFD0782','2013-07-16 14:52','FAIL' union all
select 'DYD3272AT89FD0785','2013-07-16 15:14','FAIL'
go
--测试语句
;with cte as(
select [时间段]=CONVERT(char(15),TestTime,120)+'0'
,[产量]=COUNT(*)
,[合格数量]=SUM(case when result='PASS' then 1 else 0 end)
from tb with(nolock)
group by CONVERT(char(15),TestTime,120)+'0'
)select *,CONVERT(numeric(5,2),[合格数量]*1.0/[产量]) as [良率] from cte
go
drop table tb
/**
时间段 产量 合格数量 良率
---------------- ----------- ----------- ---------------------------------------
2013-07-09 16:20 13 13 1.00
2013-07-09 16:30 3 2 0.67
2013-07-16 14:40 4 2 0.50
2013-07-16 14:50 3 0 0.00
2013-07-16 15:10 1 0 0.00
(5 行受影响)
*/
------解决方案--------------------
create table #table(a varchar(10),b varchar(10),c varchar(10) )
insert into #table
select * from (
select 1 as 'a',1 as 'b',1 as 'c'
union
select 1 as 'a',2 as 'b',2 as 'c'
union
select 2 as 'a',2 as 'b',2 as 'c'
union
select 2 as 'a',2 as 'b',1 as 'c'
) as w
select * from #table
declare @i int
set @i=1
while @i<5
begin
declare @s datetime
set @s=DATEADD(ss,3,getdate())--当前时间加三秒执行一次
waitfor time @s--定时执行函数