oracle一个简单问题

我想实现这样一个查询 ，在一个时间段内， 如果连续3天都有记录则统计出来，如果不是连续3天得记录，则不记录

SQL code

贴一些实例数据!

病人id 就诊时间time
A 20111101
A 20111102
A 20111103
A 20111106
A 20111110
A 20111114
A 20111116
B 20111004
B 20111015
B 20111016
B 20111017
B 20111018
B 20111108
B 20111113
B 20111117
C 20110925
C 20110926
C 20110927
C 20110928
C 20110929
C 20111102
C 20111103
现在想实现这样的效果，如果一个病人连续三天有就诊记录，则把它统计出来，

SQL code

结果数据显现是什么样子?

病人姓名 连续三天有就诊记录的次数
A 1
B 1
C 1

SQL code

select t1.* from T t1,T t2,T t3
where t1.id=t2.id and t1.id=t3.id
and Trunc(t1.time)+1=Trunc(t2.time)
and Trunc(t1.time)+2=Trunc(t3.time);
试试

SQL code

select name, count(name)
  from (select b.name, min(b.time) Start_HM, max(b.time) End_HM
          from (select a.*, TO_NUMBER(a.time - rownum) cc
                  from (select * from tab1 order by name, time) a) b
         group by b.name, b.cc)
 where TO_NUMBER(End_HM - Start_HM) >= 2 group by name

说明：name是病人姓名，time是number类型

SQL code

select name, count(name)
  from (select b.name, min(b.time) Start_HM, max(b.time) End_HM
          from (select a.*, TO_NUMBER(a.time - rownum) cc
                  from (select * from tab1 order by name, time) a) b
         group by b.name, b.cc)
 where TO_NUMBER(End_HM - Start_HM) >= 2 group by name

提示无效数字 ，我的表时间是date类型的

SQL code

select name,count(name)
  from (select b.name, min( to_number(to_char(b.time,'yyyymmdd'))) Start_HM, max( to_number(to_char(b.time,'yyyymmdd'))) End_HM
          from (select a.*,  to_number(to_char(a.time,'yyyymmdd') - rownum)  cc
                  from (select * from tab1 order by name, time) a) b
         group by b.name, b.cc)
 where TO_NUMBER(End_HM - Start_HM) >=2 group by name

转化一下