试一下吧,未测试select num1,num2 from
(
select lpad(to_number(lag(num2)over(order by num1))+1,8,'0') num1,
lpad(to_number(num1)-1,8,'0') num2
from
(select num1,num2
from T
where num1<=v_end and num2>=v_start
union all
select lpad(to_number(v_end)+1,8,'0'),null from dual
where not exists (select 1 from T where num1<=v_end and num2>=v_end)
)
)where num1<=num2
(
select lpad(to_number(lag(num2)over(order by num1))+1,8,'0') num1,
lpad(to_number(num1)-1,8,'0') num2
from
(select num1,num2
from T
where num1<=v_end and num2>=v_start
union all
select lpad(to_number(v_end)+1,8,'0'),null from dual
where not exists (select 1 from T where num1<=v_end and num2>=v_end)
)
)where num1<=num2
select num1,num2 from
(
select lpad(to_number(lag(num2,1,v_start)over(order by num1))+1,8,'0') num1,
lpad(to_number(num1)-1,8,'0') num2
from
(select num1,num2
from T
where num1<=v_end and num2>=v_start
union all
select lpad(to_number(v_end)+1,8,'0'),null from dual
where not exists (select 1 from T where num1<=v_end and num2>=v_end)
)
)where num1<=num2
语句中的v_start 和v_end 替换为你的起止参数
select num1,num2 from
(
select lpad(to_number(lag(num2,1,to_number(v_start)-1)over(order by num1))+1,8,'0') num1,
lpad(to_number(num1)-1,8,'0') num2
from
(select num1,num2
from T
where num1<=v_end and num2>=v_start
union all
select lpad(to_number(v_end)+1,8,'0'),null from dual
)
)where num1<=num2
order by num1