select state,main(date) as starttime,max(date) as endtime from( select *,rid = row_number() over (partition by state order by date) from tb )t group by state,ID-rid
select state,min(date) as starttime,max(date) as endtime --min打成main了 from( select *,rid = row_number() over (partition by state order by date) from tb )t group by state,ID-rid
tb是你的表,你把tb换成你的表明,里面看有什么字段名要改的不。
create table #tb ([ID] int,state int,[date] datetime) --你的表 insert #tb select 1,1,'2011-04-04 12:12:12' union all select 2,1,'2011-04-04 12:12:13' union all select 3,1,'2011-04-04 12:12:14' union allselect 4,2,'2011-04-04 12:12:15' union all select 5,2,'2011-04-04 12:12:16' union all select 6,2,'2011-04-04 12:12:17' union allselect 7,0,'2011-04-04 12:12:18' union all select 8,0,'2011-04-04 12:12:19' union allselect 9,1,'2011-04-04 12:12:20' union all select 10,1,'2011-04-04 12:12:21' union allselect 11,0,'2011-04-04 12:12:22' union all select 12,0,'2011-04-04 12:12:23' union all select 12,0,'2011-04-04 12:12:24' declare @state as int set @state=1 --状态参数 declare @tb table(num int identity(1,1),[ID] int,state int,[date] datetime) insert @tb select [ID],state,[date] from #tb where state=@state order by [date] select state,min([date]) as starttime,max([date]) as endtime from @tb group by state,dateadd(ss,-num,[date])
select state,main(date) as starttime,max(date) as endtime
from(
select *,rid = row_number() over (partition by state order by date)
from tb
)t
group by state,ID-rid
'main' 不是可以识别的 内置函数名称。
消息 102,级别 15,状态 1,第 5 行
't' 附近有语法错误。
select state,min(date) as starttime,max(date) as endtime --min打成main了
from(
select *,rid = row_number() over (partition by state order by date)
from tb
)t
group by state,ID-rid
create table #tb
([ID] int,state int,[date] datetime) --你的表
insert #tb
select 1,1,'2011-04-04 12:12:12' union all
select 2,1,'2011-04-04 12:12:13' union all
select 3,1,'2011-04-04 12:12:14' union allselect 4,2,'2011-04-04 12:12:15' union all
select 5,2,'2011-04-04 12:12:16' union all
select 6,2,'2011-04-04 12:12:17' union allselect 7,0,'2011-04-04 12:12:18' union all
select 8,0,'2011-04-04 12:12:19' union allselect 9,1,'2011-04-04 12:12:20' union all
select 10,1,'2011-04-04 12:12:21' union allselect 11,0,'2011-04-04 12:12:22' union all
select 12,0,'2011-04-04 12:12:23' union all
select 12,0,'2011-04-04 12:12:24' declare @state as int
set @state=1 --状态参数
declare @tb table(num int identity(1,1),[ID] int,state int,[date] datetime)
insert @tb
select [ID],state,[date] from #tb where state=@state order by [date]
select state,min([date]) as starttime,max([date]) as endtime from @tb group by state,dateadd(ss,-num,[date])