日期:2014-05-17 浏览次数:20953 次
select ta.mobile from, ta.maxt, tb.maxt from
(select mobile, max(time) maxt, action from a group by mobile where action=1) ta,
(select mobile, max(time) maxt, action from a group by mobile where action=2) tb
where ta.mobile=tb.mobile and round(to_date(tb.maxt,'yyyymmdd')-to_date(ta.maxt,'yyyymmdd')) = 4;
不知道行不行,反正我没试。
with t as(
select '13631521739' as moble,TO_DATE('20120915','YYYYMMDD') as time,'1' as action,'0' as re_value from dual
union
select '15143728915' as moble,TO_DATE('20120915','YYYYMMDD') as time,'1' as action,'0' as re_value from dual
union
select '15143728915' as moble,TO_DATE('20120915','YYYYMMDD') as time,'1' as action,'2' as re_value from dual
union
select '15143728915' as moble,TO_DATE('20120918','YYYYMMDD') as time,'2' as action,'0' as re_value from dual
union
select '13631521739' as moble,TO_DATE('20120925','YYYYMMDD') as time,'2' as action,'0' as re_value from dual
)
--四天以及之内的
SELECT A.MOBLE,A.TIME,B.TIME,TRUNC(B.TIME-A.TIME) AS DAY
FROM T A, T B
WHERE A.MOBLE = B.MOBLE
AND A.RE_VALUE != 2 AND B.RE_VALUE != 2
AND A.ACTION = 1 AND B.ACTION= 2
AND TRUNC(B.TIME-A.TIME)<=4
--四天之后的