ORACLE统计连续N天的记录

p7nv27q462

　　第一步:排序
SELECT * FROM TABLE1 ORDER BY ID 
第二步:日期-ROWNUM如果相同的话认为他们是连续的,然后GORUP BY一下
SELECT A.*,(SUBSTR(TO_CHAR(A.RQ,'YYYYMMDD'),0,4)*12+SUBSTR(TO_CHAR(A.RQ,''YYYYMMDD'),5,2) -ROWNUM)  DAYS 
　　FROM (SELECT * FROM TABLE1 ORDER BY ID) A
第三步:分别求出连续区间,让他们相减求出大于N的值
SELECT B.ID,MAX(B.RQ),MIN(B.RQ), DAYS
FROM (SELECT A.*,(SUBSTR(TO_CHAR(A.RQ,'YYYYMMDD'),0,4)*12+SUBSTR(TO_CHAR(A.RQ,''YYYYMMDD'),5,2) -ROWNUM)  DAYS 
FROM (SELECT * FROM TABLE1 ORDER BY ID) A  ) B
GROUP BY B.ID, B.DAYS 
　　HAVING COUNT(*)>N