with a as (select 'a' name,1 code,date'2015-1-1' time from dual union all select 'b' name,2 code,date'2015-1-6' time from dual union all select 'c' name,3 code,date'2016-1-9' time from dual union all select 'd' name,4 code,date'2016-2-1' time from dual union all select 'e' name,5 code,date'2016-3-7' time from dual union all select 'f' name,6 code,date'2016-8-1' time from dual ), t as (select name,code,time,row_number()over(order by time) rn from a --where id=1 ) select t1.name,t1.code,t2.* from t t1,t t2 where t1.rn=t2.rn-1 and (abs(t1.time-t2.time)<=60 or abs(t2.time-t1.time)<=60) order by 1 ;另外也可以用lead和lag函数来满足你的需求,具体用法你搜一下或者等#1
有点问题,改一下 [code=sql]with a as (select 'a' name,1 code,date'2015-1-1' time from dual union all select 'b' name,2 code,date'2015-1-6' time from dual union all select 'c' name,3 code,date'2016-1-9' time from dual union all select 'd' name,4 code,date'2016-2-1' time from dual union all select 'e' name,5 code,date'2016-3-7' time from dual union all select 'f' name,6 code,date'2016-8-1' time from dual ), t as (select name,code,time,row_number()over(order by time) rn from a --where id=1 ) select t1.name,t1.code from t t1,t t2 where t1.rn=t2.rn-1 and (t1.time-t2.time<=60 or abs(t2.time-t1.time)<=60) order by 1 ;
使用开窗函数:with a as (select 'a' name,1 code,date'2015-1-1' time from dual union all select 'b' name,2 code,date'2015-1-6' time from dual union all select 'c' name,3 code,date'2016-1-9' time from dual union all select 'd' name,4 code,date'2016-2-1' time from dual union all select 'e' name,5 code,date'2016-3-7' time from dual union all select 'f' name,6 code,date'2016-8-1' time from dual ), t as (select name,code,time,count(1) over (order by time desc range interval '60' day preceding) +count(1) over (order by time range interval '60' day preceding) cc from a) select name,code,time from t where cc>2;
with a as
(select 'a' name,1 code,date'2015-1-1' time from dual union all
select 'b' name,2 code,date'2015-1-6' time from dual union all
select 'c' name,3 code,date'2016-1-9' time from dual union all
select 'd' name,4 code,date'2016-2-1' time from dual union all
select 'e' name,5 code,date'2016-3-7' time from dual union all
select 'f' name,6 code,date'2016-8-1' time from dual
),
t as
(select name,code,time,row_number()over(order by time) rn
from a
--where id=1
)
select t1.name,t1.code,t2.*
from t t1,t t2
where t1.rn=t2.rn-1
and (abs(t1.time-t2.time)<=60 or abs(t2.time-t1.time)<=60)
order by 1
;另外也可以用lead和lag函数来满足你的需求,具体用法你搜一下或者等#1
有点问题,改一下
[code=sql]with a as
(select 'a' name,1 code,date'2015-1-1' time from dual union all
select 'b' name,2 code,date'2015-1-6' time from dual union all
select 'c' name,3 code,date'2016-1-9' time from dual union all
select 'd' name,4 code,date'2016-2-1' time from dual union all
select 'e' name,5 code,date'2016-3-7' time from dual union all
select 'f' name,6 code,date'2016-8-1' time from dual
),
t as
(select name,code,time,row_number()over(order by time) rn
from a
--where id=1
)
select t1.name,t1.code
from t t1,t t2
where t1.rn=t2.rn-1
and (t1.time-t2.time<=60 or abs(t2.time-t1.time)<=60)
order by 1
;
(select 'a' name,1 code,date'2015-1-1' time from dual union all
select 'b' name,2 code,date'2015-1-6' time from dual union all
select 'c' name,3 code,date'2016-1-9' time from dual union all
select 'd' name,4 code,date'2016-2-1' time from dual union all
select 'e' name,5 code,date'2016-3-7' time from dual union all
select 'f' name,6 code,date'2016-8-1' time from dual
),
t as (select name,code,time,count(1) over (order by time desc range interval '60' day preceding)
+count(1) over (order by time range interval '60' day preceding) cc from a)
select name,code,time from t where cc>2;
参考http://blog.csdn.net/thinkscape/article/details/8290894