Oracle求值的连续范围

1o16k459q9

　　         首先带上原文的链接：http://www.itpub.net/thread-1873736-1-1.html。
　　有数据如下：
　　

with tmp_t as(  select  1 as id ,'aaa' as v_name from dual union all
select  2 as id ,'aaa' as v_name from dual union all
select  3 as id ,'aaa' as v_name from dual union all
select  5 as id ,'aaa' as v_name from dual union all
select  6 as id ,'bbb' as v_name from dual union all
select  7 as id ,'bbb' as v_name from dual union all
select  8 as id ,'ccc' as v_name from dual union all
select  9 as id ,'zzz' as v_name from dual union all
select 11 as id ,'zzz' as v_name from dual union all
select 13 as id ,'zzz' as v_name from dual union all
select 14 as id ,'zzz' as v_name from dual )
　　

      求v_name的连续区间。
　　 期望的结果：
　　

       容易理解的写法:
　　

select v_name,
decode(count(*), 1, to_char(min(id)), min(id) || '-' || max(id)) b
from (select id, v_name, max(rn) over(partition by v_name order by id) rn
from (select id,
v_name,
decode(id - lag(id)
over(partition by v_name order by id),
1,
0,
id) rn
from tmp_t))
group by v_name, rn
order by v_name, rn;
　　
　　简单的写法: 
　　

select v_name,
decode(to_char(mi),to_char(ma) ,to_char(ma),to_char(mi) || '-' || to_char(ma)) b
from (select v_name, min(id) mi, max(id) ma
from (select t.* from tmp_t t order by t.v_name, t.id)
group by v_name, id - rownum
order by v_name);
　　简洁的写法: 
　　

--连续ID减行号的差值一定是相同的
select v_name,
decode(count(*), 1, to_char(min(id)), min(id) || '-' || max(id)) b
from (select t.*, id - row_number() over(partition by v_name order by id) gp
from tmp_t t)
group by v_name, gp
order by v_name
　　求连续值范围一般都是先求差值，在按差值分组，如下所示:
　　

with tmp_t as(
select to_date('201401','yyyy-mm') as v_month,100  as v_value from dual union all
select to_date('201402','yyyy-mm'),100 from dual union all
select to_date('201404','yyyy-mm'),100 from dual union all
select to_date('201405','yyyy-mm'),300 from dual union all
select to_date('201406','yyyy-mm'),100 from dual union all
select to_date('201311','yyyy-mm'),110 from dual union all
select to_date('201310','yyyy-mm'),110 from dual union all
select to_date('201407','yyyy-mm'),100 from dual )
select v_value,decode(to_char(min(v_month), 'yyyymm'),
to_char(max(v_month), 'yyyymm'),
to_char(max(v_month), 'yyyymm'),
to_char(min(v_month), 'yyyymm') || '-' ||
to_char(max(v_month), 'yyyymm')) v_range
from (select v_month,
v_value,
to_number(to_char(add_months(v_month,
-1 * row_number()
over(partition by v_value order by
v_month)),
'yyyymm')) diff
from tmp_t)
group by diff, v_value
order by 1;
　　结果为：
　　

   
　　全文完。