mysql中带有time_stamp差异的select查询

Question

我想要一个查询来查找小于某个值和大于某个值的数据，时间戳间隔为5分钟。

例如20.4是<= 26.0和25.5也是<= 26.0，但time_stamp值相差只有3分钟，所以我想选择20.4,40.1,22.3,32.2

20.4    October, 26 2013 13:12:22+0000
25.5    October, 26 2013 13:15:22+0000
40.1    October, 26 2013 13:35:22+0000
22.3    October, 26 2013 13:43:22+0000
19.78   October, 26 2013 13:45:22+0000
32.2    October, 26 2013 13:51:22+0000

20.4    October, 26 2013 13:12:22+0000
40.1    October, 26 2013 13:35:22+0000
22.3    October, 26 2013 13:43:22+0000
32.2    October, 26 2013 13:51:22+0000

请查找sql fiddle link

Answer 1

试试这个：

SELECT type,time_stamp
FROM (
   select type,time_stamp,
          if(time_stamp > @prev_tm + interval 5 minute, 
             (@prev_tm:=time_stamp), @prev_tm ) prev_tm
   from table1,
   (
      SELECT (@prev_tm:= min(time_stamp) - interval 999 minute)
      FROM table1
   ) init_vars
   where time_stamp >= '2013-10-26 13:12:22'
   and (type <= 26 or type >= 30)
   order by time_stamp
) sub_query
WHERE time_stamp = prev_tm

演示：http://www.sqlfiddle.com/#!2/e67b3/24