下面的情况要怎么写sql,查询比较方便

wh1004 2010-10-28 09:20:20
这是一个GPS定位历史数据表,有以下几个关键字段
设备编号(varchar(20)) 经度(float) 纬度(float) 日期时间(datetime)
Num X Y Time

每台设备每分钟有一条记录,历史表中大约有2000万条以上数据,一百多台设备
我现在是想查询其中50台设备,在某一时间段(time1 --- time2)某区域(x1,y1 --- x2,y2)的定位数据,每台车最多一条信息。要怎样写sql比较方便,查询快啊
...全文
84 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
abuying 2010-11-03
[Quote=引用 8 楼 wh1004 的回复:]

我用以下sql语句
SQL code

select StatusName=H.Status,H.Speed,H.Direction,H.RegionInfo,H.PhoneNum,D.VehicleNo,D.FrameNumber,
H.GpsX,H.GpsY,H.LoginTime,JL='',D.DeviceType,D.MapImage
FROM DeviceInfo AS D I……
[/Quote]

你的那个not exists不是取最小logintime吗?为何不直接写
回复
--小F-- 2010-11-03
[Quote=引用 8 楼 wh1004 的回复:]
我用以下sql语句

SQL code

select StatusName=H.Status,H.Speed,H.Direction,H.RegionInfo,H.PhoneNum,D.VehicleNo,D.FrameNumber,
H.GpsX,H.GpsY,H.LoginTime,JL='',D.DeviceType,D.MapImage
FROM DeviceInfo AS……
[/Quote]

连接字段加索引
回复
wh1004 2010-11-03
我用以下sql语句

select StatusName=H.Status,H.Speed,H.Direction,H.RegionInfo,H.PhoneNum,D.VehicleNo,D.FrameNumber,
H.GpsX,H.GpsY,H.LoginTime,JL='',D.DeviceType,D.MapImage
FROM DeviceInfo AS D INNER JOIN HISTRACEINFO H ON D.PhoneNum =H.PhoneNum
WHERE H.GpsX between 114.061855948303 and 114.07638225
and H.GpsY between 29.5338619526608 and 29.541250669012
and H.LoginTime between '2010-11-03 00:00' and '2010-11-03 18:00'
and D.PhoneNum in(p1,p2,p3,p4,p5,p6)
and not exists (select 1 from HISTRACEINFO where GpsX between 114.061855948303 and 114.07638225
and GpsY between 29.5338619526608 and 29.541250669012 and
LoginTime between '2010-11-03 00:00' and '2010-11-03 18:00' and PhoneNum=H.PhoneNum
and LoginTime > H.LoginTime)

查询几台还比较快,但一旦查询50-60台就很慢大约的2-3分钟,有没有优化的方法啊
回复
dawugui 2010-10-28
[Quote=引用楼主 wh1004 的回复:]
这是一个GPS定位历史数据表,有以下几个关键字段
设备编号(varchar(20)) 经度(float) 纬度(float) 日期时间(datetime)
Num X Y Time

每台设备每分钟有一条记录,历史表中大约有2000万条以上数据,一百多台设备
我现在是想查询其中50台设备,在某一时间段(time1 --- time2)某区域(x1,y1 --- x2,y2)的定位数据,每台车最多一条信息。要怎样写sql比较方便,查询快啊 [/Quote]

--取最大该时间段内的最大时间
select t.* from tb t where 日期时间 between time1 and time2 and 经度 between x1 and x2 and 纬度 between y1 and y2
and not exists (select 1 from tb where 日期时间 between time1 and time2 and 经度 between x1 and x2 and 纬度 between y1 and y2 and 设备编号 = t.设备编号 and 日期时间 > t.日期时间)

--取最大该时间段内的最小时间
select t.* from tb t where 日期时间 between time1 and time2 and 经度 between x1 and x2 and 纬度 between y1 and y2
and not exists (select 1 from tb where 日期时间 between time1 and time2 and 经度 between x1 and x2 and 纬度 between y1 and y2 and 设备编号 = t.设备编号 and 日期时间 < t.日期时间)

--取最大该时间段内的任何一个时间
select t.* from tb t where 日期时间 between time1 and time2 and 经度 between x1 and x2 and 纬度 between y1 and y2
and 日期时间 = (select top 1 日期时间 from tb where 日期时间 between time1 and time2 and 经度 between x1 and x2 and 纬度 between y1 and y2 and 设备编号 = t.设备编号 order by newid())
回复
abuying 2010-10-28

select * from  tb where 日期时间 between time1 and time2
and 经度 between x1 and x2 and 纬度 between y1 and y2
and time=(select max([time] from tb group by num)

回复
SQLCenter 2010-10-28
select * from tb t where Num in (...) and X between x1 and x2 and Y between y1 and y2 and Time between Time1 and Time2
and not exists (select 1 from tb where Num=t.Num and X between x1 and x2 and Y between y1 and y2 and Time between Time1 and Time2 and Time>t.time)

?
回复
王向飞 2010-10-28
给点测试数据和你的查询条件,还有你想要的结果。
回复
wh1004 2010-10-28
不是前50条数据,我的表里面保存的是100多台设备的历史数据,我现在需要的是50台以指定编号的设备(a1,a2,a3,........,a50)经过区域的最大时间的一条数据
回复
SQLCenter 2010-10-28
select top 50 * from tb where 日期时间 between time1 and time2
and 经度 between x1 and x2 and 纬度 between y1 and y2

?
回复
王向飞 2010-10-28
某区域(x1,y1 --- x2,y2) 你这是一个方形的数据集合吗?要是那就有难度了。
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-28 09:20
社区公告
暂无公告