求助：求一函数或语句，判断数字是否连续，连续几个

[wei234405106]

我需要得到最长的连续数字是连续几个，从哪个开始，到哪个结束
如：12,11,10,18,13,19
这里面连续的数字是10,11,12,13 和 18,19
我要的结果是4，10，13
谢谢！！

[软件钢琴师]

不知道你的具体用途，给你一篇文章或许你可以找出你需要的东西 http://www.itpub.net/thread-719692-1-1.html

[流浪川]

create or replace procedure ngame(v_var1 VARCHAR2,v_a OUT VARCHAR2,v_b OUT VARCHAR2,v_c OUT NUMBER) IS
create_sql VARCHAR2(1024);
drop_sql VARCHAR2(1024);
purge_sql VARCHAR2(1024);
insert_sql VARCHAR2(1024);
update_sql VARCHAR2(1024);
var_child VARCHAR2(1024);
var_number VARCHAR2(1024);
cur_sql VARCHAR2(1024):='select a from temp_game';
cur_update SYS_REFCURSOR;
num_sum NUMBER;
begin
  create_sql:='CREATE TABLE temp_game(a NUMBER,b NUMBER,c NUMBER)';
  drop_sql:='drop table temp_game';
  purge_sql:='purge table temp_game';
  insert_sql :='insert into temp_game(a) values ';
  Execute  IMMEDIATE create_sql;
  var_child:=v_var1;
  --初始化表数据
  LOOP
    IF length(var_child)=0 OR var_child IS NULL THEN
    EXIT;
    END IF;
    IF instr(var_child,',')>0 THEN
      var_number:=substr(var_child,1,instr(var_child,',')-1);    
      var_child:=substr(var_child,instr(var_child,',')+1);
    ELSE
      var_number:=var_child;
      var_child:='';
    END IF;
    insert_sql:='insert into temp_game(a) values (' || var_number || ')';
    EXECUTE IMMEDIATE insert_sql ;
    COMMIT;
  END LOOP;
  EXECUTE IMMEDIATE 'update temp_game set b=a,c=1';
  COMMIT;
  EXECUTE IMMEDIATE 'select count(1) from temp_game' INTO num_sum;
  LOOP
    IF num_sum=0 THEN
    EXIT;
    ELSE
    EXECUTE IMMEDIATE 'update temp_game t set t.b=b+1,c=c+1 where exists(select 1 from temp_game k where k.a=t.b+1)';
    num_sum:=num_sum-1;
    COMMIT;
    END IF;
  END LOOP;
  EXECUTE IMMEDIATE 'select a,b,c from (select a,b,c from temp_game order by c desc) where rownum=1' INTO v_a,v_b,v_c;
  Execute  IMMEDIATE drop_sql;
  Execute  IMMEDIATE purge_sql;
end ngame;

[流浪川]

笨方法，但是功能算实现了，就当娱乐啦。。

[yinan9]

with tb as
 (select 10 as nm
    from dual
  union all
  select 12 as nm
    from dual
  union all
  select 11 as nm
    from dual
  union all
  select 13 as nm
    from dual
  union all
  select 18 as nm
    from dual
  union all
  select 17 as nm from dual
  
  )
select count(1), max(nm), min(nm)
  from (select nm, rank() over(order by nm asc) row_num from tb)
 group by nm - row_num
having count (1) > 2;

[yinan9]

引用 4 楼 yinan9 的回复:

SQL code?123456789101112131415161718192021222324with tb as (select 10 as nm from dual union all select 12 as nm from dual union all select 11 as nm from dual union all sele……

最大值，最小值顺序反了，楼主调整下吧 逻辑就是，先对数据排序得到序号，（连续的数字） 减去 （序号） 应该是等值，group by之即可

[wei234405106]

引用 4 楼 yinan9 的回复:

SQL code? 123456789101112131415161718192021222324 with tb as (select 10 as nm from dual union all select 12 as nm from dual union all select 11 as nm from dual union all ……

多谢，简单明了。。

[wei234405106]

引用 2 楼 hidanger521 的回复:

SQL code? 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051 create or replace procedure ngame(v_var1 VARCHAR2,v_a OUT VARCHAR2,v_b OUT VARCHAR2,v_……

谢谢，实现功能就是王道。。

[wei234405106]

引用 1 楼 taiguang 的回复:

不知道你的具体用途，给你一篇文章或许你可以找出你需要的东西 http://www.itpub.net/thread-719692-1-1.html

多谢

[wallace0809]

试试这个，应该简单点

with tb as (select 10 as nm     from dual   union all  select 12 as nm     from dual   union all  select 11 as nm     from dual   union all  select 13 as nm     from dual   union all  select 18 as nm     from dual   union all  select 17 as nm from dual       ) 
select max(nm) - min(nm) + 1, min(nm), max(nam)
  from (select nm from tb order by nm)
group by nm - rownum