sql 找出日期连续的记录

Question

大家帮忙想想这个sql怎么写。小弟不胜感激我有这样一个表用来记录电表的故障，
id ammeter_id     time 
1    1            2010-1-1
2    1            2010-7-20
3    1            2010-7-21
4    1            2010-7-22
5    2            2010-7-1
6    2            2010-7-2
7    2            2010-7-22如果我向搜索7月22号以前连续发生故障的电表，有人会每天记录有故障的电表
则返回  ammeter_id     time       last_time
           1          2010-7-22    3         (因为20、21、22 连续三天)
           2          2010-7-22    1         （因为只有22号连续一天）

Answer 1

 应该是这样的:SELECT a.ammeter_id , MAX(a.time) as last_time , COUNT(1)+1 FROM rest a INNER JOIN rest b WHERE a.id != b.id AND a.ammeter_id = b.ammeter_id 
AND  a.time  = DATE_ADD( b.time,INTERVAL 1 DAY )  
 GROUP BY a.ammeter_id

Answer 2

测试过的select aid,
       count(times) as coun,
       min(time) as starttime,
       max(time) as maxtime
  from (select v.*,
               case
                 when time1 = 1 then
                  time1
                 when time2 = 1 then
                  time2
                 else
                  nvl(time1, -1)
               end as times
          from (select v1.*,
                       v1.time - v2.time as time1,
                       v3.time - v1.time as time2
                  from (select id,
                               aid,
                               time,
                               row_number() over(partition by aid order by time) as rowindex
                          from temp) v1,
                       (select id,
                               aid,
                               time,
                               row_number() over(partition by aid order by time) as rowindex
                          from temp) v2,
                       (select id,
                               aid,
                               time,
                               row_number() over(partition by aid order by time) as rowindex
                          from temp) v3
                 where v1.aid = v2.aid(+)
                   and v1.rowindex = v2.rowindex(+) + 1
                   and v1.aid = v3.aid(+)
                   and v1.rowindex = v3.rowindex(+) - 1) v
         order by aid, time)
 where times = 1
 group by aid

Answer 3

小改一下select aid,
       count(times) as coun,
       min(time) as starttime,
       max(time) as maxtime
  from (select v.*,
               case
                 when time1 = 1 then
                  time1
                 when time2 = 1 then
                  time2
                 else
                  0
               end as times
          from (select v1.*,
                       v1.time - v2.time as time1,
                       v3.time - v1.time as time2
                  from (select id,
                               aid,
                               time,
                               row_number() over(partition by aid order by time) as rowindex
                          from temp) v1,
                       (select id,
                               aid,
                               time,
                               row_number() over(partition by aid order by time) as rowindex
                          from temp) v2,
                       (select id,
                               aid,
                               time,
                               row_number() over(partition by aid order by time) as rowindex
                          from temp) v3
                 where v1.aid = v2.aid(+)
                   and v1.rowindex = v2.rowindex(+) + 1
                   and v1.aid = v3.aid(+)
                   and v1.rowindex = v3.rowindex(+) - 1) v
         order by aid, time)
 --where times = 1
 group by aid,times

Answer 4

  with temp as(
  select 1 id,1 ammeter_id,to_date('2010-01-01','yyyy-mm-dd') time1 from dual
  union all
  select 2 id,1 ammeter_id,to_date('2010-7-20','yyyy-mm-dd') time1 from dual
  union all
  select 3 id,1 ammeter_id,to_date('2010-7-21','yyyy-mm-dd') time1 from dual
  union all
  select 4 id,1 ammeter_id,to_date('2010-7-22','yyyy-mm-dd') time1 from dual
  union all
  select 5 id,2 ammeter_id,to_date('2010-7-1','yyyy-mm-dd') time1 from dual
  union all
  select 6 id,2 ammeter_id,to_date('2010-7-2','yyyy-mm-dd') time1 from dual
  union all
  select 7 id,2 ammeter_id,to_date('2010-7-22','yyyy-mm-dd') time1 from dual 
  )
select ammeter_id,sum(decode(rn - (to_date('2010-07-22','yyyy-mm-dd')-time1),1,1,0)) from(
  select ammeter_id,time1,lead(time1) over(partition by ammeter_id order by time1 desc) time2,
  row_number() over(partition by ammeter_id order by time1 desc) rn from temp
) group by ammeter_id

Answer 5

谢谢各位，小弟不胜感激，
djvfe 能帮忙解释下么，因为真实的表比这要复杂些，我还得改改才能用。谢谢

Answer 6

http://topic.csdn.net/u/20091104/18/536ea809-2fa8-4761-9db4-c1eb46890084.html

Answer 7

这样转换一下
to_date(to_char(time,'yyyyMMdd'),'yyyyMMdd')
再来相减，就会得到整数的结果

Answer 8

SQL> select * from test;
 
                   ID TIMES
--------------------- -----------
                    1 2010-7-1
                    1 2010-7-2
                    1 2010-7-3
                    1 2010-7-4
                    2 2010-7-6
                    2 2010-7-7
                    2 2010-7-8
                    2 2010-7-9
 
8 rows selected
 
SQL> 
SQL> WITH  t1 AS (SELECT * FROM test WHERE TIMEs>=to_date('20100312','yyyymmdd') )
  2  SELECT ID, ROOT_TIME, COUNT(1)
  3    FROM (SELECT id,CONNECT_BY_ROOT(TIMES) ROOT_TIME, TIMES
  4            FROM T1 A
  5           START WITH NOT EXISTS
  6           (SELECT 1 FROM T1 B WHERE B.TIMES = A.TIMES + 1)
  7          CONNECT BY NOCYCLE PRIOR A.TIMES = A.TIMES + 1 AND PRIOR a.id=a.id)
  8   GROUP BY id,ROOT_TIME
  9   HAVING COUNT(1)>1;
 
                   ID ROOT_TIME     COUNT(1)
--------------------- ----------- ----------
                    1 2010-7-4             4
                    2 2010-7-9             4
 
SQL> 

Answer 9

不需要having了
SQL> select * from test;
 
                   ID TIMES
--------------------- -----------
                    1 2010-7-1
                    1 2010-7-2
                    1 2010-7-3
                    1 2010-7-4
                    2 2010-7-6
                    2 2010-7-7
                    2 2010-7-8
                    2 2010-7-9
 
8 rows selected
 
SQL> 
SQL> WITH  t1 AS (SELECT * FROM test WHERE TIMEs>=to_date('20100312','yyyymmdd') )
  2  SELECT ID, ROOT_TIME, COUNT(1)
  3    FROM (SELECT id,CONNECT_BY_ROOT(TIMES) ROOT_TIME, TIMES
  4            FROM T1 A
  5           START WITH NOT EXISTS
  6           (SELECT 1 FROM T1 B WHERE B.TIMES = A.TIMES + 1)
  7          CONNECT BY NOCYCLE PRIOR A.TIMES = A.TIMES + 1 AND PRIOR a.id=a.id)
  8   GROUP BY id,ROOT_TIME;
 
                   ID ROOT_TIME     COUNT(1)
--------------------- ----------- ----------
                    1 2010-7-4             4
                    2 2010-7-9             4
 
SQL> 

Answer 10

不需要having了
SQL> select * from test;
 
                   ID TIMES
--------------------- -----------
                    1 2010-7-1
                    1 2010-7-2
                    1 2010-7-3
                    1 2010-7-4
                    2 2010-7-6
                    2 2010-7-7
                    2 2010-7-8
                    2 2010-7-9
 
8 rows selected
 
SQL> 
SQL> WITH  t1 AS (SELECT * FROM test WHERE TIMEs>=to_date('20100312','yyyymmdd') )
  2  SELECT ID, ROOT_TIME, COUNT(1)
  3    FROM (SELECT id,CONNECT_BY_ROOT(TIMES) ROOT_TIME, TIMES
  4            FROM T1 A
  5           START WITH NOT EXISTS
  6           (SELECT 1 FROM T1 B WHERE B.TIMES = A.TIMES + 1)
  7          CONNECT BY NOCYCLE PRIOR A.TIMES = A.TIMES + 1 AND PRIOR a.id=a.id)
  8   GROUP BY id,ROOT_TIME;
 
                   ID ROOT_TIME     COUNT(1)
--------------------- ----------- ----------
                    1 2010-7-4             4
                    2 2010-7-9             4
 
SQL> 

Answer 11

兄弟 ，谢谢了，这个能在改改么，当有多组（超过两个）连续的日起就不行了。
例如
id ammeter_id time  
1 1 2010-1-1
2 1 2010-6-20
3 1 2010-6-214 1 2010-7-20
5 1 2010-7-216 1 2010-8-20
7 1 2010-8-21