求SQL语句，高分

Question

领导让做一个车辆轨迹查询的SQL，表VelTrace结构如下：posTime（Date，定位时间）、lon(经度)、lat（纬度）、UintName（车名，字符串），每个UnitName每隔1秒有一组定位数据。现在想做轨迹回放，每秒钟回放原来过程中10分钟的点。这样就想把所有参加回放的点取出来，即从车辆A起点开始、10分钟后的点为第一个点、20分钟后的点为第二个点，中间的点不需要；以上以一辆车为例，要求可实现多个车的混合查询。请问怎样在oracle中写SQl语句，才能把这些满足条件的点放在一个记录集中，一次性全部取出来？同时又可以过滤掉不需要的点？

Answer 1

可以对posTime进取模，取子查询中能被10分钟整除的记录，再按照posTime排序即可。

Answer 2

一楼的方法可行，
可以如下（v_time为你要回放的开始时间）
select * from VelTrace where mod((posTime-v_time)*1440,10)=0

Answer 3

回楼上，按照这种方法只能取出车辆A的。
因为车辆A和车辆B的开始时间不是完全的一致的。
怎样才能取多个车的呢？

Answer 4

select * 
from VelTrace v
where (select mod((v.posTime-min(posTime))*1440, 10)
 from  VelTrace where v.UintName=UintName) = 0;

Answer 5

今天做了个测试，还有问题，主要是每个UnitName每隔1秒有一组定位数据
而 mod((posTime-v_time)*1440,10)=0会把1分钟的数据都取出来...另外，数据是实际监测的，不是理论上的数据，每辆车的v_time是不一样的

Answer 6

-- 每辆车从定位开始时间的运行轨迹
select * 
from VelTrace v
where (select mod((v.posTime-min(posTime))*1440*60, 600)
 from  VelTrace where v.UintName=UintName) = 0;-- 可以在 where 语句后加 and UintName=... and posTime=... 条件限定车辆和开始时间

Answer 7

drop table VelTrace purge;CREATE TABLE VelTrace(
posTime date,
lon varchar2(20),
lat varchar2(20),
UintName varchar2(20)
);INSERT INTO VelTrace(posTime,lon,lat,UintName) VALUES(to_date('2007-11-03 13:05:52','yyyy-mm-dd hh24:mi:ss'),'116:28E','39:54N','京B3662');
INSERT INTO VelTrace(posTime,lon,lat,UintName) VALUES(to_date('2008-01-24 10:04:02','yyyy-mm-dd hh24:mi:ss'),'117:10E','39:10N','津NB888');
INSERT INTO VelTrace(posTime,lon,lat,UintName) VALUES(to_date('2009-03-09 08:56:59','yyyy-mm-dd hh24:mi:ss'),'112:55E','28:12N','湘G0054');COMMIT;set serveroutput on;
DECLARE
  v_posTime DATE;
  v_lon VelTrace.lon%type;
  v_lat VelTrace.lat%type;
  v_UintName VelTrace.UintName%type;  cursor cur is SELECT posTime,lon,lat,UintName from VelTrace order by UintName;
  v_rand_line NUMBER(18,0); -- 每个车牌号将要插入的数据行数(在1400到8888之间)
  v_lon_rand NUMBER(18,0);  -- 车辆经度变化随机数
  v_lat_rand NUMBER(18,0);  -- 车辆纬度变化随机数
  v_lon_new NUMBER(18,0);   -- 车辆新的经度数
  v_lat_new NUMBER(18,0);   -- 车辆新的纬度数BEGIN
  for rur in cur loop
    v_posTime := rur.posTime;
    v_lon := rur.lon;
    v_lat := rur.lat;
    v_UintName := rur.UintName;    SELECT dbms_random.value(1400,8888) INTO v_rand_line FROM dual;
    FOR i in 1 .. v_rand_line LOOP
      SELECT dbms_random.value(1,4) INTO v_lon_rand FROM DUAL;
      SELECT dbms_random.value(1,3) INTO v_lat_rand FROM DUAL;
      v_lon_new := to_number(substr(v_lon,5,2)) + v_lon_rand;
      v_lat_new := to_number(substr(v_lat,instr(v_lat,':')+1,2)) + v_lat_rand;
      dbms_output.put_line(v_lat||' substr(v_lat,4,2) '||substr(v_lat,4,2) );      -- select to_number(substr(v_lat,4,2))+v_lat_rand into v_lat from dual;      v_posTime := to_date(to_char(v_posTime+1/86400,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss');      if v_lon_new >= 100 then
        v_lon := to_char(to_number(substr(v_lon,1,3))+1)||':'||lpad(to_char(mod(v_lon_new,100)),2,'0')||'E';
      else
        v_lon := to_char(to_number(substr(v_lon,1,3)))||':'||lpad(to_char(v_lon_new),2,'0')||'E';
      end if;      if v_lat_new >= 100 then
        v_lat := to_char(to_number(substr(v_lat,1,instr(v_lat,':')-1))+1)||':'||lpad(to_char(mod(v_lat_new,100)),2,'0')||'N';
      else
        v_lat := to_char(to_number(substr(v_lat,1,instr(v_lat,':')-1)))||':'||lpad(to_char(v_lat_new),2,'0')||'N';
      end if;      INSERT INTO VelTrace(posTime,lon,lat,UintName) VALUES(v_posTime,v_lon,v_lat,v_UintName);        END LOOP;
  END LOOP;
  COMMIT;
END;
/-- 查看一下刚才插入的数据(看日期是不是以1秒递增)
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select * from (
select * from VelTrace order by UintName, posTime ) t
where rownum<=20;alter table VelTrace drop index VelTrace_ix;
create index VelTrace_ix on VelTrace(uintname,postime);select t1.posTime, t1.lon, t1.lat, t1.uintname
from VelTrace t1
where exists (select t2.uintName, min(t2.postime) as postime
from VelTrace t2
       where t2.uintName=t1.uintName
group by t2.uintName
      having mod((t1.postime-min(t2.postime))*24*60*60,600)<0.5 -- 由于微秒字段可能有些许误差
                                                                        -- (此处若用=0，等式左边必须用trunc()函数将其变成整型，才正确)
-- 为提高运行效率，尽量少用函数，所以用<0.5)
     )
order by t1.uintname, t1.posTime;
POSTIME             LON                                      LAT                                      UINTNAME
------------------- ---------------------------------------- ---------------------------------------- ------------------
2007-11-03 13:05:52 116:28E                                  39:54N                                   京B3662
2007-11-03 13:15:52 131:27E                                  51:56N                                   京B3662
2007-11-03 13:25:52 146:20E                                  63:18N                                   京B3662
2007-11-03 13:35:52 161:20E                                  75:12N                                   京B3662
2007-11-03 13:45:52 176:02E                                  87:18N                                   京B3662
2007-11-03 13:55:52 190:99E                                  99:01N                                   京B3662
2007-11-03 14:05:52 206:02E                                  111:85N                                  京B3662
2007-11-03 14:15:52 220:96E                                  123:69N                                  京B3662
2007-11-03 14:35:52 250:96E                                  148:12N                                  京B3662
2007-11-03 14:45:52 266:15E                                  160:24N                                  京B3662
2007-11-03 14:55:52 281:11E                                  172:53N                                  京B3662
2008-01-24 10:04:02 117:10E                                  39:10N                                   津NB888
2008-01-24 10:14:02 132:05E                                  50:96N                                   津NB888
2008-01-24 10:24:02 147:27E                                  63:14N                                   津NB888
2008-01-24 10:34:02 162:08E                                  75:27N                                   津NB888
2008-01-24 10:44:02 177:07E                                  87:51N                                   津NB888
2009-03-09 08:56:59 112:55E                                  28:12N                                   湘G0054
2009-03-09 09:06:59 127:49E                                  39:78N                                   湘G0054
2009-03-09 09:16:59 142:83E                                  51:86N                                   湘G0054
2009-03-09 09:26:59 157:42E                                  63:89N                                   湘G0054
2009-03-09 09:36:59 172:39E                                  76:22N                                   湘G0054
2009-03-09 09:46:59 187:48E                                  88:28N                                   湘G0054
2009-03-09 09:56:59 202:27E                                  100:29N                                  湘G0054
2009-03-09 10:06:59 217:22E                                  112:27N                                  湘G0054已选择24行。

Answer 8

--  下面这个查询语句，比上面更准确：
select t1.posTime, t1.lon, t1.lat, t1.uintname
from VelTrace t1
where exists (select t2.uintName, min(t2.postime) as postime
from VelTrace t2
       where t2.uintName=t1.uintName
group by t2.uintName
      having mod(to_number(to_char(min(t2.postime),'mi')),10)=mod(to_number(to_char(t1.posTime,'mi')),10)
                and to_char(min(t2.postime),'ss')=to_char(t1.postime,'ss')
     )
order by t1.uintname, t1.posTime;
POSTIME             LON                                      LAT                                      UINTNAME
------------------- ---------------------------------------- ---------------------------------------- -----------------------
2007-11-03 13:05:52 116:28E                                  39:54N                                   京B3662
2007-11-03 13:15:52 131:27E                                  51:56N                                   京B3662
2007-11-03 13:25:52 146:20E                                  63:18N                                   京B3662
2007-11-03 13:35:52 161:20E                                  75:12N                                   京B3662
2007-11-03 13:45:52 176:02E                                  87:18N                                   京B3662
2007-11-03 13:55:52 190:99E                                  99:01N                                   京B3662
2007-11-03 14:05:52 206:02E                                  111:85N                                  京B3662
2007-11-03 14:15:52 220:96E                                  123:69N                                  京B3662
2007-11-03 14:25:52 236:09E                                  135:87N                                  京B3662
2007-11-03 14:35:52 250:96E                                  148:12N                                  京B3662
2007-11-03 14:45:52 266:15E                                  160:24N                                  京B3662
2007-11-03 14:55:52 281:11E                                  172:53N                                  京B3662
2008-01-24 10:04:02 117:10E                                  39:10N                                   津NB888
2008-01-24 10:14:02 132:05E                                  50:96N                                   津NB888
2008-01-24 10:24:02 147:27E                                  63:14N                                   津NB888
2008-01-24 10:34:02 162:08E                                  75:27N                                   津NB888
2008-01-24 10:44:02 177:07E                                  87:51N                                   津NB888
2009-03-09 08:56:59 112:55E                                  28:12N                                   湘G0054
2009-03-09 09:06:59 127:49E                                  39:78N                                   湘G0054
2009-03-09 09:16:59 142:83E                                  51:86N                                   湘G0054
2009-03-09 09:26:59 157:42E                                  63:89N                                   湘G0054
2009-03-09 09:36:59 172:39E                                  76:22N                                   湘G0054
2009-03-09 09:46:59 187:48E                                  88:28N                                   湘G0054
2009-03-09 09:56:59 202:27E                                  100:29N                                  湘G0054
2009-03-09 10:06:59 217:22E                                  112:27N                                  湘G0054已选择25行。

Answer 9

-- 其实最好用临时表：将每个车牌号的起始时间存储到临时表，再去链接查询，其效率应该高一些!
-- 效率更高的SQL语句：
select  tt.posTime,tt.lon, tt.lat, tt.uintname
from (
select t1.posTime,t1.lon, t1.lat, t1.uintname, t2.MinPosTime
from VelTrace t1 inner join (select t.uintName, min(t.posTime) as MinPosTime from VelTrace t group by t.uintName ) t2
on t1.uintname=t2.uintname
 ) tt
where mod(to_number(to_char(tt.posTime,'mi')),10)=mod(to_number(to_char(tt.MinposTime,'mi')),10)
  and to_char(tt.postime,'ss')=to_char(tt.Minpostime,'ss')
order by tt.uintname, tt.posTime;
POSTIME             LON                                      LAT                                      UINTNAME
------------------- ---------------------------------------- ---------------------------------------- ----------
2007-11-03 13:05:52 116:28E                                  39:54N                                   京B3662
2007-11-03 13:15:52 131:27E                                  51:56N                                   京B3662
2007-11-03 13:25:52 146:20E                                  63:18N                                   京B3662
2007-11-03 13:35:52 161:20E                                  75:12N                                   京B3662
2007-11-03 13:45:52 176:02E                                  87:18N                                   京B3662
2007-11-03 13:55:52 190:99E                                  99:01N                                   京B3662
2007-11-03 14:05:52 206:02E                                  111:85N                                  京B3662
2007-11-03 14:15:52 220:96E                                  123:69N                                  京B3662
2007-11-03 14:25:52 236:09E                                  135:87N                                  京B3662
2007-11-03 14:35:52 250:96E                                  148:12N                                  京B3662
2007-11-03 14:45:52 266:15E                                  160:24N                                  京B3662
2007-11-03 14:55:52 281:11E                                  172:53N                                  京B3662
2008-01-24 10:04:02 117:10E                                  39:10N                                   津NB888
2008-01-24 10:14:02 132:05E                                  50:96N                                   津NB888
2008-01-24 10:24:02 147:27E                                  63:14N                                   津NB888
2008-01-24 10:34:02 162:08E                                  75:27N                                   津NB888
2008-01-24 10:44:02 177:07E                                  87:51N                                   津NB888
2009-03-09 08:56:59 112:55E                                  28:12N                                   湘G0054
2009-03-09 09:06:59 127:49E                                  39:78N                                   湘G0054
2009-03-09 09:16:59 142:83E                                  51:86N                                   湘G0054
2009-03-09 09:26:59 157:42E                                  63:89N                                   湘G0054
2009-03-09 09:36:59 172:39E                                  76:22N                                   湘G0054
2009-03-09 09:46:59 187:48E                                  88:28N                                   湘G0054
2009-03-09 09:56:59 202:27E                                  100:29N                                  湘G0054
2009-03-09 10:06:59 217:22E                                  112:27N                                  湘G0054已选择25行。

Answer 10

-- 如果起始点不需要的话：
select  tt.posTime,tt.lon, tt.lat, tt.uintname
from (
select t1.posTime,t1.lon, t1.lat, t1.uintname, t2.MinPosTime
from VelTrace t1 inner join (select t.uintName, min(t.posTime) as MinPosTime from VelTrace t group by t.uintName ) t2
on t1.uintname=t2.uintname
 ) tt
where tt.posTime>tt.MinPosTime -- 加上这个条件，就OK啦
  and mod(to_number(to_char(tt.posTime,'mi')),10)=mod(to_number(to_char(tt.MinposTime,'mi')),10)
  and to_char(tt.postime,'ss')=to_char(tt.Minpostime,'ss')
order by tt.uintname, tt.posTime;

Answer 11

如果不太注重效率或者数据量本身不大的话,connect by结合lead,tag就可以弄出来

Answer 12

感谢大家的关心！就是今天拿去给领导测试，出了个小意外。领导说数据是实测的，不是只有1秒1组定位数据的，还有1分钟1组定位数据，甚至10分钟一组数据的，这种情况下，不可能实现完美的mod(to_number(to_char(tt.posTime,'mi')),10)=mod(to_number(to_char(tt.MinposTime,'mi')),10)大虾们还有什么别的好办法没？
比如数据为：
POSTIME                              UINTNAME
------------------- ---------------------------------------- 
2007-11-03 13:05:52                     京B3662
2007-11-03 13:15:57                     京B3662
2007-11-03 13:26:03                     京B3662
2007-11-03 13:36:01                    京B3662
2007-11-03 13:45:55                     京B3662

Answer 13

-- 悲哀：就不知道活学活用，变化一点就不知道用啦，
-- 要是有一万种取片段选择，你是不是要写一万个SQL语句呢？

Answer 14

-- 见过悲哀的，没见过你这样悲哀的，如果你是这样不知道活学活用的话，你的问题永远无法解决!-- t1.posTime,t1.lon, t1.lat, t1.uintname, t2.MinPosTime
-- 这里的 两个时间相减(得到秒数)，然后再去取模(简述：mod(tim1-time2),your_seonds))=0嘛，
-- 这个模是变化的，你想怎么取就怎么取-- 比如说：5分钟，那么：your_secnods=300；如果是每过4分钟59秒，那么：your_secnods=299-- 依此类推..............

Answer 15

select t.posTime, t.lon, t.lat, t.uintname
from (
  select t1.posTime, t1.lon, t1.lat, t1.uintname, t2.MinPosTime, 
         to_number((t1.posTime-t2.MinPosTime)*24*60*60) as Seconds -- 先求出相隔秒数
  from VelTrace t1 inner join (select t.uintName, min(t.posTime) as MinPosTime from VelTrace t group by t.uintName ) t2
  on t1.uintname=t2.uintname
 ) t
where mod(round(t.seconds,0),300)=0 -- 对相隔秒数取模(300秒：即每5分钟取一片段)
order by t.uintname, t.posTime;POSTIME             LON          LAT          UINTNAME
------------------- ------------ ------------ ------------
2007-11-03 13:05:52 116:28E      39:54N       京B3662
2007-11-03 13:10:52 123:83E      45:41N       京B3662
2007-11-03 13:15:52 131:16E      51:59N       京B3662
2007-11-03 13:20:52 138:68E      57:64N       京B3662
2007-11-03 13:25:52 146:18E      63:68N       京B3662
2007-11-03 13:30:52 153:99E      69:73N       京B3662
2007-11-03 13:35:52 161:32E      75:70N       京B3662
2007-11-03 13:40:52 168:83E      81:80N       京B3662
2007-11-03 13:45:52 176:48E      87:94N       京B3662
2007-11-03 13:50:52 183:93E      94:07N       京B3662
2007-11-03 13:55:52 191:42E      99:96N       京B3662
2007-11-03 14:00:52 199:06E      106:01N      京B3662
2007-11-03 14:05:52 206:65E      111:95N      京B3662
2007-11-03 14:10:52 213:95E      117:92N      京B3662
2008-01-24 10:04:02 117:10E      39:10N       津NB888
2008-01-24 10:09:02 124:55E      45:01N       津NB888
2008-01-24 10:14:02 131:82E      50:87N       津NB888
2008-01-24 10:19:02 139:36E      56:89N       津NB888
2008-01-24 10:24:02 146:93E      62:95N       津NB888
2008-01-24 10:29:02 154:23E      69:00N       津NB888
2008-01-24 10:34:02 161:85E      74:90N       津NB888
2008-01-24 10:39:02 169:46E      80:88N       津NB888
2008-01-24 10:44:02 176:78E      86:91N       津NB888
2008-01-24 10:49:02 184:15E      93:00N       津NB888
2008-01-24 10:54:02 191:61E      99:06N       津NB888
2008-01-24 10:59:02 198:89E      105:05N      津NB888
2008-01-24 11:04:02 206:39E      111:15N      津NB888
2008-01-24 11:09:02 213:90E      117:09N      津NB888
2008-01-24 11:14:02 221:30E      122:85N      津NB888
2008-01-24 11:19:02 228:97E      128:98N      津NB888
2008-01-24 11:24:02 237:10E      135:09N      津NB888
2008-01-24 11:29:02 244:64E      141:09N      津NB888
2008-01-24 11:34:02 252:06E      147:22N      津NB888
2008-01-24 11:39:02 259:56E      153:30N      津NB888
2009-03-09 08:56:59 112:55E      28:12N       湘G0054
2009-03-09 09:01:59 119:99E      34:20N       湘G0054
2009-03-09 09:06:59 127:48E      40:33N       湘G0054
2009-03-09 09:11:59 135:05E      46:30N       湘G0054
2009-03-09 09:16:59 142:68E      52:26N       湘G0054
2009-03-09 09:21:59 150:32E      58:27N       湘G0054
2009-03-09 09:26:59 158:01E      64:17N       湘G0054
2009-03-09 09:31:59 165:49E      70:14N       湘G0054
2009-03-09 09:36:59 173:15E      76:12N       湘G0054
2009-03-09 09:41:59 180:28E      82:12N       湘G0054
2009-03-09 09:46:59 187:81E      88:08N       湘G0054
2009-03-09 09:51:59 195:32E      94:04N       湘G0054
2009-03-09 09:56:59 202:83E      99:95N       湘G0054
2009-03-09 10:01:59 210:38E      105:80N      湘G0054
2009-03-09 10:06:59 217:96E      111:71N      湘G0054
2009-03-09 10:11:59 225:28E      117:53N      湘G0054
2009-03-09 10:16:59 232:52E      123:12N      湘G0054
2009-03-09 10:21:59 240:01E      129:02N      湘G0054已选择52行。------------------------------------------------------------------------------------------select t.posTime, t.lon, t.lat, t.uintname
from (
  select t1.posTime, t1.lon, t1.lat, t1.uintname, t2.MinPosTime, 
         to_number((t1.posTime-t2.MinPosTime)*24*60*60) as Seconds -- 先求出相隔秒数
  from VelTrace t1 inner join (select t.uintName, min(t.posTime) as MinPosTime from VelTrace t group by t.uintName ) t2
  on t1.uintname=t2.uintname
 ) t
where mod(round(t.seconds,0),299)=0 -- 对相隔秒数取模(299秒：即每4分钟59秒取一片段)
order by t.uintname, t.posTime;
POSTIME             LON          LAT          UINTNAME
------------------- ------------ ------------ ------------
2007-11-03 13:05:52 116:28E      39:54N       京B3662
2007-11-03 13:10:51 123:82E      45:38N       京B3662
2007-11-03 13:15:50 131:08E      51:55N       京B3662
2007-11-03 13:20:49 138:59E      57:59N       京B3662
2007-11-03 13:25:48 146:07E      63:62N       京B3662
2007-11-03 13:30:47 153:84E      69:65N       京B3662
2007-11-03 13:35:46 161:14E      75:56N       京B3662
2007-11-03 13:40:45 168:69E      81:65N       京B3662
2007-11-03 13:45:44 176:26E      87:75N       京B3662
2007-11-03 13:50:43 183:72E      93:89N       京B3662
2007-11-03 13:55:42 191:17E      99:76N       京B3662
2007-11-03 14:00:41 198:76E      105:83N      京B3662
2007-11-03 14:05:40 206:37E      111:70N      京B3662
2007-11-03 14:10:39 213:58E      117:68N      京B3662
2008-01-24 10:04:02 117:10E      39:10N       津NB888
2008-01-24 10:09:01 124:53E      44:99N       津NB888
2008-01-24 10:14:00 131:79E      50:84N       津NB888
2008-01-24 10:18:59 139:30E      56:85N       津NB888
2008-01-24 10:23:58 146:83E      62:86N       津NB888
2008-01-24 10:28:57 154:09E      68:90N       津NB888
2008-01-24 10:33:56 161:68E      74:77N       津NB888
2008-01-24 10:38:55 169:30E      80:71N       津NB888
2008-01-24 10:43:54 176:58E      86:76N       津NB888
2008-01-24 10:48:53 183:93E      92:80N       津NB888
2008-01-24 10:53:52 191:31E      98:84N       津NB888
2008-01-24 10:58:51 198:67E      104:85N      津NB888
2008-01-24 11:03:50 206:11E      110:92N      津NB888
2008-01-24 11:08:49 213:62E      116:82N      津NB888
2008-01-24 11:13:48 220:91E      122:58N      津NB888
2008-01-24 11:18:47 228:54E      128:71N      津NB888
2008-01-24 11:23:46 236:72E      134:72N      津NB888
2008-01-24 11:28:45 244:23E      140:75N      津NB888
2008-01-24 11:33:44 251:61E      146:85N      津NB888
2008-01-24 11:38:43 259:10E      152:93N      津NB888
2009-03-09 08:56:59 112:55E      28:12N       湘G0054
2009-03-09 09:01:58 119:97E      34:18N       湘G0054
2009-03-09 09:06:57 127:43E      40:30N       湘G0054
2009-03-09 09:11:56 134:97E      46:24N       湘G0054
2009-03-09 09:16:55 142:58E      52:16N       湘G0054
2009-03-09 09:21:54 150:19E      58:18N       湘G0054
2009-03-09 09:26:53 157:88E      64:03N       湘G0054
2009-03-09 09:31:52 165:30E      70:00N       湘G0054
2009-03-09 09:36:51 172:94E      75:97N       湘G0054
2009-03-09 09:41:50 180:09E      81:91N       湘G0054
2009-03-09 09:46:49 187:60E      87:88N       湘G0054
2009-03-09 09:51:48 195:02E      93:80N       湘G0054
2009-03-09 09:56:47 202:52E      99:71N       湘G0054
2009-03-09 10:01:46 210:06E      105:55N      湘G0054
2009-03-09 10:06:45 217:60E      111:44N      湘G0054
2009-03-09 10:11:44 224:90E      117:25N      湘G0054
2009-03-09 10:16:43 232:11E      122:84N      湘G0054
2009-03-09 10:21:42 239:62E      128:64N      湘G0054
2009-03-09 10:26:41 246:68E      134:49N      湘G0054已选择53行。

Answer 16

-- 以下是不用子查询的方法：
-- (随着数据量的增大，其执行时间越来越长，所以当你所取的时间片段能被60(分)整除时(如：1、2、3、6、10、15、20、30分钟)，建议采用我第一种方法)-- 以下是以5分钟为时间片段间隔，两种方法的对比：select t1.posTime, t1.lon, t1.lat, t1.uintname
 from VelTrace t1 inner join (select t.uintName, min(t.posTime) as MinPosTime from VelTrace t group by t.uintName ) t2
   on t1.uintname=t2.uintname
where mod(round(to_number((t1.posTime-t2.MinPosTime)*86400),0),300)=0
order by t1.uintname, t1.posTime;
POSTIME             LON          LAT          UINTNAME
------------------- ------------ ------------ ---------
2007-11-03 13:05:52 116:28E      39:54N       京B3662
2007-11-03 13:10:52 123:93E      45:70N       京B3662
2007-11-03 13:15:52 131:48E      51:66N       京B3662
2007-11-03 13:20:52 139:22E      57:76N       京B3662
2007-11-03 13:25:52 146:98E      63:72N       京B3662
2007-11-03 13:30:52 154:64E      69:68N       京B3662
2007-11-03 13:35:52 161:75E      75:91N       京B3662
2007-11-03 13:40:52 169:38E      82:16N       京B3662
2007-11-03 13:45:52 176:84E      88:32N       京B3662
2007-11-03 13:50:52 184:13E      94:34N       京B3662
2007-11-03 13:55:52 191:66E      100:68N      京B3662
2007-11-03 14:00:52 199:29E      106:78N      京B3662
2007-11-03 14:05:52 206:72E      112:59N      京B3662
2007-11-03 14:10:52 214:46E      118:65N      京B3662
2007-11-03 14:15:52 221:99E      124:67N      京B3662
2007-11-03 14:20:52 229:43E      130:73N      京B3662
2007-11-03 14:25:52 236:70E      136:87N      京B3662
2007-11-03 14:30:52 244:00E      143:13N      京B3662
2007-11-03 14:35:52 251:60E      149:24N      京B3662
2008-01-24 10:04:02 117:10E      39:10N       津NB888
2008-01-24 10:09:02 124:61E      45:10N       津NB888
2008-01-24 10:14:02 132:03E      51:11N       津NB888
2008-01-24 10:19:02 139:45E      56:95N       津NB888
2008-01-24 10:24:02 146:83E      62:69N       津NB888
2008-01-24 10:29:02 154:02E      68:83N       津NB888
2008-01-24 10:34:02 161:84E      74:86N       津NB888
2008-01-24 10:39:02 169:47E      80:89N       津NB888
2008-01-24 10:44:02 176:91E      86:81N       津NB888
2008-01-24 10:49:02 184:31E      93:02N       津NB888
2008-01-24 10:54:02 191:84E      99:02N       津NB888
2008-01-24 10:59:02 199:27E      105:01N      津NB888
2008-01-24 11:04:02 207:00E      110:93N      津NB888
2008-01-24 11:09:02 214:36E      116:76N      津NB888
2008-01-24 11:14:02 221:64E      122:87N      津NB888
2008-01-24 11:19:02 229:12E      128:97N      津NB888
2008-01-24 11:24:02 236:65E      135:11N      津NB888
2008-01-24 11:29:02 244:26E      141:08N      津NB888
2008-01-24 11:34:02 251:82E      147:37N      津NB888
2008-01-24 11:39:02 259:34E      153:36N      津NB888
2008-01-24 11:44:02 266:62E      159:32N      津NB888
2008-01-24 11:49:02 273:96E      165:27N      津NB888
2008-01-24 11:54:02 281:61E      171:36N      津NB888
2008-01-24 11:59:02 289:29E      177:12N      津NB888
2008-01-24 12:04:02 296:67E      183:14N      津NB888
2008-01-24 12:09:02 304:07E      189:09N      津NB888
2008-01-24 12:14:02 311:43E      195:00N      津NB888
2009-03-09 08:56:59 112:55E      28:12N       湘G0054
2009-03-09 09:01:59 119:96E      34:20N       湘G0054
2009-03-09 09:06:59 127:41E      40:08N       湘G0054
2009-03-09 09:11:59 134:80E      46:11N       湘G0054
2009-03-09 09:16:59 142:08E      52:19N       湘G0054
2009-03-09 09:21:59 149:63E      58:13N       湘G0054
2009-03-09 09:26:59 157:08E      64:04N       湘G0054
2009-03-09 09:31:59 164:32E      70:12N       湘G0054
2009-03-09 09:36:59 171:56E      75:96N       湘G0054
2009-03-09 09:41:59 179:22E      81:79N       湘G0054
2009-03-09 09:46:59 187:00E      87:70N       湘G0054
2009-03-09 09:51:59 194:43E      93:84N       湘G0054
2009-03-09 09:56:59 202:13E      99:90N       湘G0054
2009-03-09 10:01:59 209:56E      105:84N      湘G0054已选择60行。已用时间:  00: 02: 34.45-------------------------------------------------------------------------------------------------------------------select  tt.posTime,tt.lon, tt.lat, tt.uintname
from (
select t1.posTime,t1.lon, t1.lat, t1.uintname, t2.MinPosTime
from VelTrace t1 inner join (select t.uintName, min(t.posTime) as MinPosTime from VelTrace t group by t.uintName ) t2
on t1.uintname=t2.uintname
 ) tt
where mod(to_number(to_char(tt.posTime,'mi')),5)=mod(to_number(to_char(tt.MinposTime,'mi')),5)
  and to_char(tt.postime,'ss')=to_char(tt.Minpostime,'ss')
order by tt.uintname, tt.posTime;
POSTIME             LON          LAT          UINTNAME
------------------- ------------ ------------ ------------
2007-11-03 13:05:52 116:28E      39:54N       京B3662
2007-11-03 13:10:52 123:93E      45:70N       京B3662
2007-11-03 13:15:52 131:48E      51:66N       京B3662
2007-11-03 13:20:52 139:22E      57:76N       京B3662
2007-11-03 13:25:52 146:98E      63:72N       京B3662
2007-11-03 13:30:52 154:64E      69:68N       京B3662
2007-11-03 13:35:52 161:75E      75:91N       京B3662
2007-11-03 13:40:52 169:38E      82:16N       京B3662
2007-11-03 13:45:52 176:84E      88:32N       京B3662
2007-11-03 13:50:52 184:13E      94:34N       京B3662
2007-11-03 13:55:52 191:66E      100:68N      京B3662
2007-11-03 14:00:52 199:29E      106:78N      京B3662
2007-11-03 14:05:52 206:72E      112:59N      京B3662
2007-11-03 14:10:52 214:46E      118:65N      京B3662
2007-11-03 14:15:52 221:99E      124:67N      京B3662
2007-11-03 14:20:52 229:43E      130:73N      京B3662
2007-11-03 14:25:52 236:70E      136:87N      京B3662
2007-11-03 14:30:52 244:00E      143:13N      京B3662
2007-11-03 14:35:52 251:60E      149:24N      京B3662
2008-01-24 10:04:02 117:10E      39:10N       津NB888
2008-01-24 10:09:02 124:61E      45:10N       津NB888
2008-01-24 10:14:02 132:03E      51:11N       津NB888
2008-01-24 10:19:02 139:45E      56:95N       津NB888
2008-01-24 10:24:02 146:83E      62:69N       津NB888
2008-01-24 10:29:02 154:02E      68:83N       津NB888
2008-01-24 10:34:02 161:84E      74:86N       津NB888
2008-01-24 10:39:02 169:47E      80:89N       津NB888
2008-01-24 10:44:02 176:91E      86:81N       津NB888
2008-01-24 10:49:02 184:31E      93:02N       津NB888
2008-01-24 10:54:02 191:84E      99:02N       津NB888
2008-01-24 10:59:02 199:27E      105:01N      津NB888
2008-01-24 11:04:02 207:00E      110:93N      津NB888
2008-01-24 11:09:02 214:36E      116:76N      津NB888
2008-01-24 11:14:02 221:64E      122:87N      津NB888
2008-01-24 11:19:02 229:12E      128:97N      津NB888
2008-01-24 11:24:02 236:65E      135:11N      津NB888
2008-01-24 11:29:02 244:26E      141:08N      津NB888
2008-01-24 11:34:02 251:82E      147:37N      津NB888
2008-01-24 11:39:02 259:34E      153:36N      津NB888
2008-01-24 11:44:02 266:62E      159:32N      津NB888
2008-01-24 11:49:02 273:96E      165:27N      津NB888
2008-01-24 11:54:02 281:61E      171:36N      津NB888
2008-01-24 11:59:02 289:29E      177:12N      津NB888
2008-01-24 12:04:02 296:67E      183:14N      津NB888
2008-01-24 12:09:02 304:07E      189:09N      津NB888
2008-01-24 12:14:02 311:43E      195:00N      津NB888
2009-03-09 08:56:59 112:55E      28:12N       湘G0054
2009-03-09 09:01:59 119:96E      34:20N       湘G0054
2009-03-09 09:06:59 127:41E      40:08N       湘G0054
2009-03-09 09:11:59 134:80E      46:11N       湘G0054
2009-03-09 09:16:59 142:08E      52:19N       湘G0054
2009-03-09 09:21:59 149:63E      58:13N       湘G0054
2009-03-09 09:26:59 157:08E      64:04N       湘G0054
2009-03-09 09:31:59 164:32E      70:12N       湘G0054
2009-03-09 09:36:59 171:56E      75:96N       湘G0054
2009-03-09 09:41:59 179:22E      81:79N       湘G0054
2009-03-09 09:46:59 187:00E      87:70N       湘G0054
2009-03-09 09:51:59 194:43E      93:84N       湘G0054
2009-03-09 09:56:59 202:13E      99:90N       湘G0054
2009-03-09 10:01:59 209:56E      105:84N      湘G0054已选择60行。已用时间:  00: 00: 00.21
-- 由上可见：两种方法，其效率相差甚远！

Answer 17

首先感谢下luoyoumou的认真指导，下面详细说下需求，以下列数据为例：原始数据如下，每三分钟一组数据POSTIME                UINTNAME
------------------- -------------------  
2007-11-03 13:05:52   京B3662
2007-11-03 13:08:57   京B3662
2007-11-03 13:12:03   京B3662
2007-11-03 13:15:08   京B3662
2007-11-03 13:18:30   京B3662
2007-11-03 13:22:00   京B3662
2007-11-03 13:25:12   京B3662
2007-11-03 13:28:03   京B3662
2007-11-03 13:31:45   京B3662
2007-11-03 13:34:38   京B3662要求每五分钟取一组数据，取出结果集应为POSTIME                UINTNAME
------------------- -------------------  
2007-11-03 13:05:52   京B3662 （13:05:52 + 0分钟）
2007-11-03 13:12:03   京B3662 （13:05:52 + 5分钟）
2007-11-03 13:18:30   京B3662 （13:05:52 + 10分钟）
2007-11-03 13:22:00   京B3662 （13:05:52 + 15分钟）
2007-11-03 13:28:03   京B3662 （13:05:52 + 20分钟）
2007-11-03 13:31:45   京B3662 （13:05:52 + 25分钟）也就是说不能采用mod函数进行数据处理，请问还有什么高见嘛？
我现在只能是把数据全取出来，用程序注意处理，感觉很傻

Answer 18

没人有高见了？再挂一天结贴了
感谢luoyoumou