如何在oracle中对序列号进行分组？

例如2000,2001,2002,2005,2006,2007和2010.

我想将连续数字分组.

我的输出应该是2000-2003,2005-2007和2010.在Oracle存储过程中有没有办法做到这一点？

18:42:15 SYSTEM@dwal> l
  1  with p as (
  2    select replace('2000,2007 and 2010',' and ',',') s,'[0-9]{4}' r from dual
  3  ),ex as (
  4    select regexp_substr(s,r,1,level) as y
  5      from p
  6    connect by level <= regexp_count(s,r)
  7  ),grp as (
  8    select connect_by_root(y) s,y
  9      from ( select e1.y y,e2.y p from ex e1,ex e2 where e1.y - 1 = e2.y(+) )
 10   connect by prior y = p
 11     start with p is null
 12  ),agg as (
 13  select listagg(s||decode(max(y),s,null,'-'||max(y)),') within group (order by s) str
 14    from grp group by s
 15  )
 16* select regexp_replace(str,regexp_count(str,')) result from agg
18:42:16 SYSTEM@dwal> /

RESULT
------------------------------
2000-2002,2005-2007 and 2010

Elapsed: 00:00:00.02

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!