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;
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;
试试这个,应该简单点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
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;
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;
逻辑就是,先对数据排序得到序号,(连续的数字) 减去 (序号) 应该是等值,group by之即可
select max(nm) - min(nm) + 1, min(nm), max(nam)
from (select nm from tb order by nm)
group by nm - rownum