某一段时间，在小于五秒钟内出现2次以上

查询
某一段时间，a在小于五秒钟内出现2次以上


with t as (
select t.a,t.b
from c_table t
where 1 = 1
and b>= to_date('2012-5-29 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
  and b<= to_date('2012-5-30 00:00:00', 'yyyy-MM-dd hh24:mi:ss') and t.a>'0'  order by t.a
)

select t.* from t where exists(
select 1 from t a where t.a=a.a and
abs(t.b-a.b)<=5/(24*60*60) and abs(t.b-a.b)>0
)









--表信息

连锁店编号 会员卡号 录入员工号  录入终端  录入时间   
2012001001 12345678 2012000001  00000001  20120510 16:29:23
2012001001 12345678 2012000001  00000001  20120510 16:30:44
2012001001 12345678 2012000001  00000001  20120510 16:39:12
2012001004 12345678 2012000002  00000001  20120510 16:41:21
2012001004 32345678 2012000002  00000002  20120510 16:43:56
2012001004 22345678 2012000002  00000001  20120510 16:46:49
2012001004 52345678 2012000002  00000002  20120510 16:47:23
2012001001 32345678 2012000003  00000001  20120510 16:49:10
2012001002 82345678 2012000003  00000002  20120510 16:53:08
2012001004 92345678 2012000003  00000003  20120510 16:57:00
2012001005 92345678 2012000003  00000002  20120510 17:01:51
2012001003 82345678 2012000003  00000003  20120510 17:02:23
查询
同一连锁店当日同一会员卡号，在相同的终端连续2次以上（含2次）录入时间=<2分钟


with t as (
select
'2012001001' a,'12345678' b,'2012000001' c,'1' d,to_date('20120510162923','yyyymmddhh24miss') e from dual union all select
'2012001001','12345678','2012000001','1',to_date('20120510163044','yyyymmddhh24miss') from dual union all select
'2012001001','12345678','2012000001','1',to_date('20120510163912','yyyymmddhh24miss')from dual union all select
'2012001004','12345678','2012000002','1',to_date('20120510164121','yyyymmddhh24miss')from dual union all select
'2012001004','32345678','2012000002','2',to_date('20120510164356','yyyymmddhh24miss')from dual union all select
'2012001004','22345678','2012000002','1',to_date('20120510164649','yyyymmddhh24miss')from dual union all select
'2012001004','52345678','2012000002','2',to_date('20120510164723','yyyymmddhh24miss')from dual union all select
'2012001001','32345678','2012000003','1',to_date('20120510164910','yyyymmddhh24miss')from dual union all select
'2012001002','82345678','2012000003','2',to_date('20120510165308','yyyymmddhh24miss')from dual union all select
'2012001004','92345678','2012000003','3',to_date('20120510165700','yyyymmddhh24miss')from dual union all select
'2012001005','92345678','2012000003','2',to_date('20120510170151','yyyymmddhh24miss')from dual union all select
'2012001003','82345678','2012000003','3',to_date('20120510170223','yyyymmddhh24miss') from dual

)

select t.* from t where exists(
select 1 from t a where t.a=a.a and t.b=a.b and t.d= a.d
and abs(t.e-a.e)<=2/(24*60) and abs(t.e-a.e)>0
)

---------------------------------
2012001001    12345678    2012000001    1    2012-5-10 16:29:23
2012001001    12345678    2012000001    1    2012-5-10 16:30:44


/*
oracle 时间相减为天数：

SELECT( to_date('20120510162923','yyyymmddhh24miss') - to_date('20120510163044','yyyymmddhh24miss'))*24 *60 *60  FROM dual
结果为天数：-0.0009375 天
化为分钟：-0.0009375 * 24 *60 = -1.35 分钟
化为秒：-1.35 分钟= -1.35 * 60 = -81秒

*/