又遇到个问题不知道怎么做
车牌 车速 时间 保持秒数
1 86 2011-01-02 11:29:30 8
1 85 2011-01-02 11:29:38 11
1 90 2011-01-02 11:29:49 5
1 87 2011-01-02 11:29:54 10
1 60 2011-01-02 11:30:04 20
1 83 2011-01-02 11:30:24 10
我怎么去得到它每一次超速的开始时间和结束时间呢我想得到的结果是
车牌,超速开始时间,超速结束时间,超速的时间,最高时速
车牌 车速 时间 保持秒数
1 86 2011-01-02 11:29:30 8
1 85 2011-01-02 11:29:38 11
1 90 2011-01-02 11:29:49 5
1 87 2011-01-02 11:29:54 10
1 60 2011-01-02 11:30:04 20
1 83 2011-01-02 11:30:24 10
我怎么去得到它每一次超速的开始时间和结束时间呢我想得到的结果是
车牌,超速开始时间,超速结束时间,超速的时间,最高时速
create table TB_VDRDATA
(
carid int,
carspeed int,
data datetime,
bcms float
)
insert into TB_Vdrdata values(1,'86','2011-01-02 11:29:30','8')
insert into TB_Vdrdata values(1,'85','2011-01-02 11:29:38 ','11')
insert into TB_Vdrdata values(1,'90','2011-01-02 11:29:49','5')
insert into TB_Vdrdata values(1,'87','2011-01-02 11:29:54','10')
insert into TB_Vdrdata values(1,'60','2011-01-02 11:30:04','20')
insert into TB_Vdrdata values(1,'83','2011-01-02 11:30:24','10')with t1 as
(
select id1=row_number() over(partition by carid order by data),* from TB_Vdrdata
)
,t2 as
(
select id2=row_number() over(partition by carid order by data),* from t1 where carspeed > 80 and bcms >= 6
)
select 车牌=carid, 超速开始时间=min(data), 超速结束时间=max(dateadd(ss,bcms,data)), 超速的时间=sum(bcms), 最高时速=max(carspeed)
from t2 group by carid, id1-id2