如何查出这样的“断号”？

[myy]

有一张表 A 的字段 为 varchar2 型, 但存放的是 16 进制的编码，如：

A000
A001
A002
...
...
EF0A
EF0B
....

该字段按16进制的值连续排列，总共有几万个，但其中少了一个（就一个），
如果只用SQL语句，如何能快速找出少了哪一个？

[山林73]

select * from(
select *
from (
select to_number(a,'xxxxx') as n1 ,...
from t1 order by a
) where rownum > n1 - ??
) where rownum < 10

返回头几条
??为代码对应起始数值

[山林73]

....
where rownum < n1 - ??


select min(a) from (
select rownum - n1 + ?? as s, a,....
from (
select to_number(a,'xxxxx') as n1 , a, ...
from t1 order by a
)
) group by s
可以返回多处断点

[ORARichard]

select * from (select rownum no,value from 表A) tmp where rawtohex(value)-rawtohex(to_char(no))<>41303000

[ORARichard]

--test

create table t(value varchar2(4));
insert into t select 'A001' from dual union select 'A002' from dual union select 'A004' from dual;

select * from t;
VALU
----
A001
A002
A004

select * from (select rownum no,value from t) tmp where rawtohex(value)-rawtohex(to_char(no))<>41303000;
NO VALU
--------- ----
3 A004

drop table t;

[ORARichard]

以上结果的第一条即为所查结果，可以用rownum=1限定，只取出所查的那条记录

select * from (select rownum no,value from 表A) tmp where rawtohex(value)-rawtohex(to_char(no))<>41303000 and rownum=1;

当然查询必须满足value是升序的

[zmgowin]

select to_number(a,'xxxxx')+1 from t1
minus
select to_number(a,'xxxxx') from t1
minus
select max(to_number(a,'xxxxx'))+1 from t1;

[wupangzi]

多用几个SQL语句，限制范围，没次一半，看看结果数据是不是对！
如select count(*) from tablename where colname >'某数值' and colname < '某数值' ;

[wupangzi]

zmgowin(隐者(龙祖宗)看了大哥的方法，突然认为这样也可以！
select a from t1 a where not exists(select * from t1 b where b.a = (to_number(a,'xxxxx')+1));

[wupangzi]

两种方法结合对大数据量的查找一定效率高！