select 'KP'|| substr('000'||(suba+r1-1),length('000'||(suba+r1-1))-2,3) as result from (select min(rownum) as r1,substr(code,3,3)-rownum as suba from (select * from test order by code) a group by substr(code,3,3)-rownum ) b where suba>0 /这个只能处理当中断一个号码。 连续断两个比较麻烦。
1 select 2 'KP'|| substr('000'||(suba+r1-1-(suba+1-rank1)), 3 length('000'||(suba+r1-1-(suba+1-rank1)))-2, 4 3) as RFrom, 5 'KP'|| substr('000'||(suba+r1-1),length('000'||(suba+r1-1))-2,3) as RTo 6 from 7 (select min(rownum) as r1,substr(code,3,3)-rownum as suba, 8 rank() over(order by substr(code,3,3)-rownum) rank1 9 from (select * from test order by code) a 10 group by substr(code,3,3)-rownum 11 ) b 12* where suba>0 SQL> /RFROM RTO -------- -------- KP005 KP005 KP008 KP008 KP010 KP011 多个连续这么表示可以么。能力有限,写的不是很好,想必效率也不行。用存储过程和临时表做会更好一点。good luck
1、数据20万,编号PK000,所以编号最多1000条 2、查询语句: select 'KP' || substr(a.r,length(r)-2,3) as code from ( select '000' || rownum r,a.* from Test a where rownum <20) a, ( select distinct code from Test) b where 'KP' || substr(a.r,length(r)-2,3) = b.code(+) and b.code is null 3、测试Create table Test ( Code varchar(20) )insert into Test values('KP001'); insert into Test values('KP002'); insert into Test values('KP003'); insert into Test values('KP004'); insert into Test values('KP006'); insert into Test values('KP007'); insert into Test values('KP009'); insert into Test values('KP010'); insert into Test values('KP001'); insert into Test values('KP002'); insert into Test values('KP003'); insert into Test values('KP004'); insert into Test values('KP006'); insert into Test values('KP007'); insert into Test values('KP009'); insert into Test values('KP010'); insert into Test values('KP001'); insert into Test values('KP002'); insert into Test values('KP003'); insert into Test values('KP004'); insert into Test values('KP006'); insert into Test values('KP007'); insert into Test values('KP009'); insert into Test values('KP010');4、结果 CODE ----- KP005 KP008 KP011 KP012 KP013 ……
(select min(rownum) as r1,substr(code,3,3)-rownum as suba
from (select * from test order by code) a
group by substr(code,3,3)-rownum
) b
where suba>0
/这个只能处理当中断一个号码。
连续断两个比较麻烦。
1 select
2 'KP'|| substr('000'||(suba+r1-1-(suba+1-rank1)),
3 length('000'||(suba+r1-1-(suba+1-rank1)))-2,
4 3) as RFrom,
5 'KP'|| substr('000'||(suba+r1-1),length('000'||(suba+r1-1))-2,3) as RTo
6 from
7 (select min(rownum) as r1,substr(code,3,3)-rownum as suba,
8 rank() over(order by substr(code,3,3)-rownum) rank1
9 from (select * from test order by code) a
10 group by substr(code,3,3)-rownum
11 ) b
12* where suba>0
SQL> /RFROM RTO
-------- --------
KP005 KP005
KP008 KP008
KP010 KP011
多个连续这么表示可以么。能力有限,写的不是很好,想必效率也不行。用存储过程和临时表做会更好一点。good luck
2、查询语句:
select 'KP' || substr(a.r,length(r)-2,3) as code
from (
select '000' || rownum r,a.* from Test a
where rownum <20) a,
(
select distinct code from Test) b
where 'KP' || substr(a.r,length(r)-2,3) = b.code(+)
and b.code is null
3、测试Create table Test
(
Code varchar(20)
)insert into Test values('KP001');
insert into Test values('KP002');
insert into Test values('KP003');
insert into Test values('KP004');
insert into Test values('KP006');
insert into Test values('KP007');
insert into Test values('KP009');
insert into Test values('KP010');
insert into Test values('KP001');
insert into Test values('KP002');
insert into Test values('KP003');
insert into Test values('KP004');
insert into Test values('KP006');
insert into Test values('KP007');
insert into Test values('KP009');
insert into Test values('KP010');
insert into Test values('KP001');
insert into Test values('KP002');
insert into Test values('KP003');
insert into Test values('KP004');
insert into Test values('KP006');
insert into Test values('KP007');
insert into Test values('KP009');
insert into Test values('KP010');4、结果
CODE
-----
KP005
KP008
KP011
KP012
KP013
……
判断一下,存在否?不存在就将该号码插入table_1中。
然后继续+,执行完成,断的号码就全在table_1里拉---