有如下需求:
我就简化描述了
table1
2个字段
ID,Cdate数据内容例如00001,2008-05-09
00002,2008-06-27
00003,2008-04-23
00001,2008-08-23
00001,2008-08-25
00003,2008-10-11
00002,2008-09-10
00003,2008-10-15我想得到相同id号的连续两次记录之间时间差小于7的记录。
也就是
00001,2008-08-23
00001,2008-08-25
00003,2008-10-11
00003,2008-10-15
不知道该如何实现。
用简单的语句能实现吗?必须用存储过程利用游标吗?
我就简化描述了
table1
2个字段
ID,Cdate数据内容例如00001,2008-05-09
00002,2008-06-27
00003,2008-04-23
00001,2008-08-23
00001,2008-08-25
00003,2008-10-11
00002,2008-09-10
00003,2008-10-15我想得到相同id号的连续两次记录之间时间差小于7的记录。
也就是
00001,2008-08-23
00001,2008-08-25
00003,2008-10-11
00003,2008-10-15
不知道该如何实现。
用简单的语句能实现吗?必须用存储过程利用游标吗?
SQL> SELECT ID,
2 CDATE
3 FROM (
4 SELECT TT.*,
5 LEAD(CDATE) OVER(PARTITION BY ID ORDER BY CDATE) LEADS,
6 COUNT(1) OVER(PARTITION BY ID) COUNTS
7 FROM TABLE_NAME TT
8 )ZZ
9 WHERE NVL(LEADS,TO_DATE('1900-01-01','YYYY-MM-DD'))-CDATE < 7
10 AND COUNTS >= 3;ID CDATE
----- -----------
00001 2008-8-23
00001 2008-8-25
00003 2008-10-11
00003 2008-10-15
2 from table1 where rowid in (select rowid
3 from (select t.*,
4 t.rowid,
5 Lag(CDATE) OVER(PARTITION BY ID ORDER BY CDATE) lag
6 FROM TABLE1 t)
7 where cdate - lag < 7
8 union
9 select rowid
10 from (select t.*,
11 t.rowid,
12 Lead(CDATE) OVER(PARTITION BY ID ORDER BY CDATE) lead
13 FROM TABLE1 t)
14 where lead - cdate < 7)
15 ;
ID CDATE
---------- -----------
1 2008-8-23
1 2008-8-25
3 2008-10-11
3 2008-10-15