求个SQL语句

[pingdesu]

a表中有字段b,其值都是以S开头的如S001,S002,S004,S006,...等，我现需要将其中未出现过的S003，S005,...等数据SELECT出来，这样的SQL怎么写呢，求大神帮助

[mayanzs]

with a1 as (select to_number(min(substr(a.b,2,4))) minb,to_number(max(substr(a.b,2,4))) maxb from a), a2 as (select 'S'||lpad(min_b+level-1) all_b from a1 connect by level<=max_b) select alln from a2 where not exists (select 1 from a where a.b=a2.all_b) order by 1;

[mayanzs]

t第一行写错了maxb,纠正为max_b with a1 as (select to_number(min(substr(a.b,2,4))) minb,to_number(max(substr(a.b,2,4))) max_b from a), a2 as (select 'S'||lpad(min_b+level-1) all_b from a1 connect by level<=max_b) select alln from a2 where not exists (select 1 from a where a.b=a2.all_b) order by 1;

[iam9823119]

SELECT REPLACE('S' || TO_CHAR(ROWNUM, '000'), ' ', '') AS B FROM DUAL CONNECT BY LEVEL <= (SELECT TO_NUMBER(MAX(SUBSTR(A.B, 2))) FROM A) MINUS SELECT B FROM A