17,140
社区成员




with t1 as
(
select 'A' c1, '2017-1-1' c2 from dual union all
select 'A' c1, '2017-10-1' c2 from dual union all
select 'A' c1, '2017-3-1' c2 from dual union all
select 'B' c1, '2017-1-1' c2 from dual union all
select 'B' c1, '2017-3-1' c2 from dual union all
select 'B' c1, '2017-8-30' c2 from dual
)
, t2 as
(
select row_number() over(order by c1, to_date(c2, 'yyyy-mm-dd')) rn, c1, to_date(c2, 'yyyy-mm-dd') c2 from t1
)
select a.c1, sum(case when b.c1 is not null and months_between(b.c2, a.c2)<=6 then 1 else 0 end)
from t2 a
left join t2 b on a.c1=b.c1 and a.rn=b.rn-1
group by a.c1