表a
code
2010-0001-SLSD
2010-0002-SLSD
2010-0003-XTGS
2010-0005-WZB
2010-0008-WDGS需要判断的是code的第6-9位,找出断缺的号
如本示例段缺的号是0004 006 007
请高手指点下。
code
2010-0001-SLSD
2010-0002-SLSD
2010-0003-XTGS
2010-0005-WZB
2010-0008-WDGS需要判断的是code的第6-9位,找出断缺的号
如本示例段缺的号是0004 006 007
请高手指点下。
select '2010-0001-SLSD' code from dual union all
select '2010-0002-SLSD' code from dual union all
select '2010-0003-XTGS' code from dual union all
select '2010-0005-WZB' code from dual union all
select '2010-0008-WDGS' code from dual
)
SELECT lpad(LEVEL, 4, '0')
FROM (SELECT to_number(MAX(substr(code, 6, 4))) maxcode FROM a)
CONNECT BY LEVEL <= maxcode
MINUS
SELECT substr(code, 6, 4) code FROM a;
insert into a(code) values('2010-0002-SLSD');
insert into a(code) values('2010-0003-XTGS');
insert into a(code) values('2010-0005-WZB');
insert into a(code) values('2010-0008-WDGS');commit;SELECT lpad(LEVEL, 4, '0')
FROM (SELECT to_number(MAX(substr(code, 6, 4))) maxcode FROM a)
CONNECT BY LEVEL <= maxcode
MINUS
SELECT substr(code, 6, 4) code FROM a;
找个数据量大点的表
select rm from (
select rownum rm from 大表) where rm <=select max(substr(code,6,4)) from table1;
minus
select substr(code,6,4) from table1;
FROM (SELECT to_number(MAX(substr(code, 6, 4))) maxcode FROM a)
CONNECT BY LEVEL <= maxcode
MINUS
SELECT substr(code, 6, 4) code FROM a;
运行提示,无效数字
-- 用 is_number() 函数可以判断!drop table a purge;
create table a(code varchar2(20));
insert into a(code) values('2010-0001-SLSD');
insert into a(code) values('2010-0002-SLSD');
insert into a(code) values('2010-0003-XTGS');
insert into a(code) values('2010-0005-WZB');
insert into a(code) values('2010-0008-WDGS');
insert into a(code) values('2010-00A8-WDGS');
commit;-- 当为“0”时表示存在 非数字 字符
select a.code, substr(a.code,6,4),
is_number(substr(a.code,6,4))
from a; CODE SUBSTR(A.CODE,6,4) IS_NUMBER(SUBSTR(A.CODE,6,4))
---------------------------------------- -------------------------------- -----------------------------
2010-0001-SLSD 0001 1
2010-0002-SLSD 0002 1
2010-0003-XTGS 0003 1
2010-0005-WZB 0005 1
2010-0008-WDGS 0008 1
2010-00A8-WDGS 00A8 0
select a.code, substr(a.code,6,4),
is_number(substr(a.code,6,4))
from a
where is_number(substr(a.code,6,4))=0;