sql - 选择单列时差小于 2 小时的行

Question

表:测试

选择同一天时差小于 2 小时的行(按日期分组)。

这里输出应该是前两行，因为前两行的时间差(18-JAN-15 01.08.40.000000000 PM - 18-JAN-15 11.21.28.000000000 AM < 2 小时)

NB: compare rows of same date.

输出:

  CREATE TABLE TEST
  ( "ID" VARCHAR2(20 BYTE), 
    "CAM_TIME" TIMESTAMP (6)
  ) 

Insert into TEST (ID,CAM_TIME) values ('1',to_timestamp('18-JAN-15 11.21.28.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into TEST (ID,CAM_TIME) values ('2',to_timestamp('18-JAN-15 01.08.40.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Insert into TEST (ID,CAM_TIME) values ('3',to_timestamp('23-JAN-15 09.18.40.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into TEST (ID,CAM_TIME) values ('4',to_timestamp('23-JAN-15 04.22.22.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Answer 1

这个自联接查询完成了这项工作:

SQL Fiddle

select distinct t1.id, t1.cam_time 
  from test t1 join test t2 on t1.rowid <> t2.rowid  
    and trunc(t1.cam_time) = trunc(t2.cam_time)
  where abs(t1.cam_time-t2.cam_time) <= 2/24
  order by t1.id

编辑:

如果 cam_time 是 time_stamp 类型，则条件应为:

where t1.cam_time between t2.cam_time - interval '2' Hour 
                      and t2.cam_time + interval '2' Hour