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

[wh1004]

这是一个GPS定位历史数据表，有以下几个关键字段
设备编号(varchar(20)) 经度(float) 纬度(float) 日期时间（datetime）
Num X Y Time

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

[王向飞]

某区域（x1,y1 --- x2,y2） 你这是一个方形的数据集合吗？要是那就有难度了。

[SQLCenter]

select top 50 * from tb where 日期时间 between time1 and time2
and 经度 between x1 and x2 and 纬度 between y1 and y2

?

[wh1004]

不是前50条数据，我的表里面保存的是100多台设备的历史数据，我现在需要的是50台以指定编号的设备（a1,a2,a3,........,a50）经过区域的最大时间的一条数据

[王向飞]

给点测试数据和你的查询条件，还有你想要的结果。

[SQLCenter]

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)

?

[abuying]

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)

[dawugui]

[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())

[wh1004]

我用以下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分钟，有没有优化的方法啊

[--小F--]

[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]

连接字段加索引

[abuying]

[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吗？为何不直接写