发一个存储过程给你参考:
CREATE PROCEDURE traffic_speed @stationno char(10),@begin_date datetime,@end_date datetime AS --传递三个参数declare @timespace int
select @timespace=datediff(hh,dateadd(hh,0,@begin_date),dateadd(hh,0,@end_date))
if @timespace<=0 return
select * into #temp1 from t_car_detect where dt_receive>=dateadd(hh,0,@begin_date) and
dt_receive<=dateadd(mi,0,@end_date) and equip_no=@stationno and status=4
--select * from #temp1
select equip_no,dt_receive,car_type,car_speed into #temp2 from #temp1
--select * from #temp2
select * into #temp4 from #temp2 order by dt_receive
--select * from #temp4
select distinct #temp2.dt_receive,
isnull((select max(car_speed) from #temp4 where car_type='1' and dt_receive=#temp2.dt_receive),0) as num1,
isnull((select max(car_speed) from #temp4 where car_type='2' and dt_receive=#temp2.dt_receive),0) as num2,
isnull((select max(car_speed) from #temp4 where car_type='3' and dt_receive=#temp2.dt_receive),0) as num3,
isnull((select max(car_speed) from #temp4 where car_type='4' and dt_receive=#temp2.dt_receive),0) as num4,
isnull((select max(car_speed) from #temp4 where car_type='5' and dt_receive=#temp2.dt_receive),0) as num5
into #temp3 from #temp2 order by dt_receive
select * from #temp3drop table #temp1
drop table #temp2
drop table #temp3
drop table #temp4
CREATE PROCEDURE traffic_speed @stationno char(10),@begin_date datetime,@end_date datetime AS --传递三个参数declare @timespace int
select @timespace=datediff(hh,dateadd(hh,0,@begin_date),dateadd(hh,0,@end_date))
if @timespace<=0 return
select * into #temp1 from t_car_detect where dt_receive>=dateadd(hh,0,@begin_date) and
dt_receive<=dateadd(mi,0,@end_date) and equip_no=@stationno and status=4
--select * from #temp1
select equip_no,dt_receive,car_type,car_speed into #temp2 from #temp1
--select * from #temp2
select * into #temp4 from #temp2 order by dt_receive
--select * from #temp4
select distinct #temp2.dt_receive,
isnull((select max(car_speed) from #temp4 where car_type='1' and dt_receive=#temp2.dt_receive),0) as num1,
isnull((select max(car_speed) from #temp4 where car_type='2' and dt_receive=#temp2.dt_receive),0) as num2,
isnull((select max(car_speed) from #temp4 where car_type='3' and dt_receive=#temp2.dt_receive),0) as num3,
isnull((select max(car_speed) from #temp4 where car_type='4' and dt_receive=#temp2.dt_receive),0) as num4,
isnull((select max(car_speed) from #temp4 where car_type='5' and dt_receive=#temp2.dt_receive),0) as num5
into #temp3 from #temp2 order by dt_receive
select * from #temp3drop table #temp1
drop table #temp2
drop table #temp3
drop table #temp4
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货