要求:需要查询出表中间隔时间在5分钟内的数据
一张表:
1,"2008-09-09 00:02:00"
2,"2008-09-09 00:02:00"
3,"2008-09-09 00:02:05"
4,"2008-09-09 00:03:00"
5,"2008-09-09 00:04:00"
6,"2008-09-09 00:05:00"
7,"2008-09-09 00:05:05"
预期结果:
1,"2008-09-09 00:02:00"
2,"2008-09-09 00:02:00"
3,"2008-09-09 00:02:05"
6,"2008-09-09 00:05:00"
7,"2008-09-09 00:05:05"
请大虾指点需要用Sybase与Oracle SQL语句得到如上结果。
一张表:
1,"2008-09-09 00:02:00"
2,"2008-09-09 00:02:00"
3,"2008-09-09 00:02:05"
4,"2008-09-09 00:03:00"
5,"2008-09-09 00:04:00"
6,"2008-09-09 00:05:00"
7,"2008-09-09 00:05:05"
预期结果:
1,"2008-09-09 00:02:00"
2,"2008-09-09 00:02:00"
3,"2008-09-09 00:02:05"
6,"2008-09-09 00:05:00"
7,"2008-09-09 00:05:05"
请大虾指点需要用Sybase与Oracle SQL语句得到如上结果。
where exists(select 1 from tb where id<>a.id and abs(datediff(mi,time,a.time))<=5)
select m.* from tb m , tb n where m.id = n.id - 1 and datediff(mi , m.time , n.time) <= 5select m.* from tb m , tb n where m.id = n.id - 1 and abs(datediff(mi , m.time , n.time) <= 5)select m.* from tb m where datediff(mi , m.time , (select top 1 time from tb n where n.time > m.time order by time desc)) <= 5