select count(1) as cnt from first where 通道数=1 and datediff(ss,起始时间,结束时间) >= 5 ???
分钟呀? select count(1) as cnt from first where 通道数=1 and datediff(mi,起始时间,结束时间) >= 5
DECLARE @First TABLE ( ID int identity(1,1), Chunnel int, TimeBegin varchar(20) ) insert into @first (Chunnel, TimeBegin) select 1,'09:23:28' union select 1,'09:23:29' union select 1,'09:23:30' union select 1,'09:23:31' union select 1,'09:23:32' union select 1,'09:23:33' union select 1,'09:23:34' union select 1,'09:23:35' union select 2,'09:23:36' union select 2,'09:23:37' union select 2,'09:23:38' union select 2,'09:23:39' union select 2,'09:23:40' union select 2,'09:23:41'declare @TimeBegin varchar(20) select @TimeBegin = MIN(TimeBegin) from @Firstselect Chunnel,COUNT(*) AS count1 from @First group by Chunnel,datediff(ss,@TimeBegin,TimeBegin)/5 --每隔五秒鍾
color=#FF0000]意思明白了,但是不知道如何在表上操作。[[/color]
用个excel的简单附图说明吧。
你这个需求真让人费解啊,不过happyflystone 基本上已经给你答案了啊,你自己组合一下就OK了啊 以下就是你需要的单独的列了: select count(1) as cnt from first where 通道数=1 and datediff(mi,起始时间,结束时间) >= 5
from first
where 通道数=1 and datediff(ss,起始时间,结束时间) >= 5
???
from first
where 通道数=1 and datediff(mi,起始时间,结束时间) >= 5
(
ID int identity(1,1),
Chunnel int,
TimeBegin varchar(20)
)
insert into @first
(Chunnel, TimeBegin)
select 1,'09:23:28'
union
select 1,'09:23:29'
union
select 1,'09:23:30'
union
select 1,'09:23:31'
union
select 1,'09:23:32'
union
select 1,'09:23:33'
union
select 1,'09:23:34'
union
select 1,'09:23:35'
union
select 2,'09:23:36'
union
select 2,'09:23:37'
union
select 2,'09:23:38'
union
select 2,'09:23:39'
union
select 2,'09:23:40'
union
select 2,'09:23:41'declare @TimeBegin varchar(20)
select @TimeBegin = MIN(TimeBegin)
from @Firstselect Chunnel,COUNT(*) AS count1
from @First
group by Chunnel,datediff(ss,@TimeBegin,TimeBegin)/5
--每隔五秒鍾
以下就是你需要的单独的列了:
select count(1) as cnt
from first
where 通道数=1 and datediff(mi,起始时间,结束时间) >= 5