DECLARE @t AS datetime --时间间隔
DECLARE @i AS int --时间间隔/*1 */
SELECT mi,Status,COUNT(*) FROM T
WHERE DATEPART(mi,[Time]) IN (DATEPART(mi,@t),DATEPART(mi,DATEADD(mi,@t,20)),DATEPART(mi,DATEADD(mi,@t,40)))
GROUP BY mi,Status/* 2 */
SELECT mi,Status,COUNT(*) FROM
(SELECT *.(SELECT COUNT(*) FROM t WHERE a.ID>ID) AS num FROM t AS a) AS b
WHERE b % @i = 0
GROUP BY mi,Status
DECLARE @i AS int --时间间隔/*1 */
SELECT mi,Status,COUNT(*) FROM T
WHERE DATEPART(mi,[Time]) IN (DATEPART(mi,@t),DATEPART(mi,DATEADD(mi,@t,20)),DATEPART(mi,DATEADD(mi,@t,40)))
GROUP BY mi,Status/* 2 */
SELECT mi,Status,COUNT(*) FROM
(SELECT *.(SELECT COUNT(*) FROM t WHERE a.ID>ID) AS num FROM t AS a) AS b
WHERE b % @i = 0
GROUP BY mi,Status
declare @recordCount int --记录数量
declare @timeInterval int --时间间隔值
declare @timeUnit varchar(10) --时间间隔单位(如 day,hour,minute,second)
declare @countInterval int --数量间隔值
declare @tempStr varchar(500)set rowcount @recordCount
set @tempStr='select ''间隔''+datepart(@TimeUnit,Time)/@timeInterval+1,
sum(case status when ''P'' then 1 else 0 end) as Pass,
sum(case status when ''F'' then 1 else 0 end) as Fail
from 表
group by ''间隔''+datepart(@TimeUnit,Time)/@timeInterval+1'
exec(@tempStr) --结果1 select id=identity(int,1,1),name,status,time into #t from 表
select '间隔'+cast(id/@countInterval+1 as varchar),
sum(case status when ''P'' then 1 else 0 end) as Pass,
sum(case status when ''F'' then 1 else 0 end) as Fail
from 表
group by '间隔'+cast(id/@countInterval+1 as varchar) --结果2
set rowcount 0
declare @timeInterval int --时间间隔值
declare @timeUnit varchar(10) --时间间隔单位(如 day,hour,minute,second)
declare @countInterval int --数量间隔值
declare @tempStr varchar(500)set rowcount @recordCount
set @tempStr='select ''间隔''+datepart(@TimeUnit,Time)/@timeInterval+1,
sum(case status when ''P'' then 1 else 0 end) as Pass,
sum(case status when ''F'' then 1 else 0 end) as Fail
from 表
group by ''间隔''+datepart(@TimeUnit,Time)/@timeInterval+1'
exec(@tempStr) --结果1 select id=identity(int,1,1),name,status,time into #t from 表
select '间隔'+cast(id/@countInterval+1 as varchar),
sum(case status when ''P'' then 1 else 0 end) as Pass,
sum(case status when ''F'' then 1 else 0 end) as Fail
from 表
group by '间隔'+cast(id/@countInterval+1 as varchar) --结果2
set rowcount 0
+cast(@timeInterval as varchar)+'+1,
sum(case status when ''P'' then 1 else 0 end) as Pass,
sum(case status when ''F'' then 1 else 0 end) as Fail
from 表
group by ''间隔''+datepart('+@TimeUnit+',Time)/'
+cast(@timeInterval as varchar)+'+1'
exec(@tempStr) --结果1