- create table test2(storeid number,createdate date);
- insert into test2(storeid,createdate) values(22,date'2015-04-21');
- insert into test2(storeid,createdate) values(24,date'2015-04-22');
- insert into test2(storeid,createdate) values(26,date'2015-04-23');
- insert into test2(storeid,createdate) values(27,date'2015-04-24');
- insert into test2(storeid,createdate) values(20,date'2015-04-25');
- insert into test2(storeid,createdate) values(22,date'2015-04-26');
- insert into test2(storeid,createdate) values(30,date'2015-05-01');
- insert into test2(storeid,createdate) values(40,date'2015-05-02');
- insert into test2(storeid,createdate) values(52,date'2015-05-03');
- insert into test2(storeid,createdate) values(20,date'2015-05-04');
- insert into test2(storeid,createdate) values(20,date'2015-05-06');
- SQL> alter session set nls_date_format='yyyy-mm-dd';
- Session altered.
- SQL> select * from test2;
- STOREID CREATEDATE
- ---------- ----------
- 22 2015-04-21
- 24 2015-04-22
- 26 2015-04-23
- 27 2015-04-24
- 20 2015-04-25
- 22 2015-04-26
- 30 2015-05-01
- 40 2015-05-02
- 52 2015-05-03
- 20 2015-05-04
- 20 2015-05-06
- 11 rows selected.
要求获得如下结果:
- CNT (ROWNUM-1)*3 MAXSTOREID MINSTOREID MAXCREATE MINCREATE
- ---------- ------------ ---------- ---------- ---------- ----------
- 3 0 26 22 2015-04-23 2015-04-21
- 3 3 27 24 2015-04-24 2015-04-22
- 3 6 27 20 2015-04-25 2015-04-23
- 3 9 27 20 2015-04-26 2015-04-24
- 3 15 52 30 2015-05-03 2015-05-01
- 3 18 52 20 2015-05-04 2015-05-02
- 6 rows selected.
语句如下, 比较乱啊, 待整理
- with
- store_grb as
- (
- select a.rn, a.storeid, a.createdate,lag_create, nvl(lag_create,createdate) as lag_create2,
- case when a.createdate=nvl(lag_create,createdate) then 1 else 0 end as grb0,
- sum(case when a.createdate=a.lag_create then 0 else 1 end)over(order by a.rn) as grb1
- from
- (
- select rownum rn,storeid,createdate,lag(createdate) over(order by rownum) + 1 as lag_create from test2 )a ),
- abc as
- (select rn,storeid,createdate,lag_create,
- grb0,grb1,count(grb1)over(partition by grb1) as cntgrb1
- from store_grb ),
- bcd2 as
- (
- select rn,3 as cnt,storeid,
- max(storeid)over(order by rn rows between 1 preceding and 1 following) maxstoreid,
- min(storeid)over(order by rn rows between 1 preceding and 1 following) minstoreid,
- createdate,lag_create,
- min(createdate)over(order by rn rows between 1 preceding and 1 following ) as mincreate,
- max(createdate)over(order by rn rows between 1 preceding and 1 following ) as maxcreate,
- grb0,grb1
- --,case when maxcreate-mincreate>=2 then 1 else 0 end
- from (select * from abc where cntgrb1>3 )),
- dddd as
- (select t.*
- from bcd2 t where t.maxcreate -t.mincreate>=2
- and t.maxcreate> t.createdate
- and t.grb0<>0),
- bbb as (select cnt,(rownum-1)*3,maxstoreid,minstoreid,maxcreate,mincreate from dddd)
- select * from bbb where maxcreate<=mincreate+2;
- CNT (ROWNUM-1)*3 MAXSTOREID MINSTOREID MAXCREATE MINCREATE
- ---------- ------------ ---------- ---------- ---------- ----------
- 3 0 26 22 2015-04-23 2015-04-21
- 3 3 27 24 2015-04-24 2015-04-22
- 3 6 27 20 2015-04-25 2015-04-23
- 3 9 27 20 2015-04-26 2015-04-24
- 3 15 52 30 2015-05-03 2015-05-01
- 3 18 52 20 2015-05-04 2015-05-02
- 6 rows selected.
来源: http://www.bubuko.com/infodetail-3113756.html