求助：求一函数或语句，判断数字是否连续，连续几个
我需要得到最长的连续数字是连续几个，从哪个开始，到哪个结束
如：12,11,10,18,13,19
这里面连续的数字是10,11,12,13 和 18,19
我要的结果是4，10，13
谢谢！！
[最优解释]
')';
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;
[其他解释]
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 ('
[其他解释]
不知道你的具体用途，给你一篇文章或许你可以找出你需要的东西
http://www.itpub.net/thread-719692-1-1.html
[其他解释]
var_number
[其他解释]
笨方法，但是功能算实现了，就当娱乐啦。。
[其他解释]
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;
[其他解释]
