select (select count(*) from userhistory where userid = userinfo.userid) count1,
       (select count(*)
          from (select * from userhistory where userid = userinfo.userid)
         start with starttime in
                    (select starttime from userinfo where userid = userinfo.userid)
        connect by prior endtime + 1 = starttime) count2,
       userinfo.userid
  from (select distinct userid from userinfo) userinfo

with userhistory as
(
     select 25 userid,date'2012-04-01' starttime,date'2012-06-30' endtime from dual
     union all 
     select 25 userid,date'2012-07-01' starttime,date'2012-08-30' endtime from dual    
     union all 
     select 25 userid,date'2012-07-01' starttime,date'9999-09-09' endtime from dual   
     union all 
     select 34 userid,date'2008-04-10' starttime,date'2008-06-30' endtime from dual   
     union all 
     select 34 userid,date'2008-07-01' starttime,date'2008-07-31' endtime from dual   
     union all 
     select 34 userid,date'2008-09-01' starttime,date'2008-09-30' endtime from dual   
     union all 
     select 34 userid,date'2008-10-01' starttime,date'2008-12-31' endtime from dual   
     union all 
     select 34 userid,date'2009-01-01' starttime,date'2009-06-30' endtime from dual  
     union all 
     select 34 userid,date'2009-07-01' starttime,date'2009-07-31' endtime from dual 
     union all 
     select 34 userid,date'2009-10-01' starttime,date'2009-12-31' endtime from dual   
)
select t1.userid,t1.starttime,t1.endtime
from (select rownum rn,a.* from userhistory a) t1,
(
select rn,userid,starttime,endtime
from 
    (
    select rownum rn,userid,starttime,endtime,lag(endtime) over(partition by userid order by rownum) t_time
    from userhistory
    ) 
where starttime-t_time<>1 
) t2
where t1.rn=t2.rn or t1.rn+1 = t2.rn
order by t1.userid,t1.starttime


    userid   starttime    endtime
----------------------------------------------
1    25    2012/7/1    2012/8/30
2    25    2012/7/1    9999/9/9
3    34    2008/7/1    2008/7/31
4    34    2008/9/1    2008/9/30
5    34    2009/7/1    2009/7/31
6    34    2009/10/1    2009/12/31