日期:2014-05-17  浏览次数:20854 次

请问一sql语句
表中一列如下:
23700202001
23700202002
23700202004
23700202005
23700202006
23700202010
23700202011
...
...
最大到
23700202999
前面八位是固定的,现在想根据后三位得到不在这个表中的数据

23700202003
23700202007
23700202008
23700202009
...
...
等等,直到最大23700202999

------解决方案--------------------
SQL> select * from tbl;

IDX
--------------------
23700202001
23700202003
23700202004
23700202005
23700202006
23700202007
23700202009

7 rows selected

SQL>
SQL> declare
2 i number;
3 cnt number;
4 begin
5 for i in 1..9
6 loop
7 select count(1) into cnt from tbl where idx = to_number( '2370020200 '||to_char(i));
8 if cnt = 0 then
9 dbms_output.put_line( 'Not exists: '||to_number( '2370020200 '||to_char(i)));
10 end if;
11 end loop;
12 end;
13 /

Not exists:23700202002
Not exists:23700202008

PL/SQL procedure successfully completed