A table has field b, their values are S at the beginning of such as S001, S002, S004 S006,... Wait, I now need to which not seen S003, S005,... Data such as SELECT, such as SQL how to write, seek help from god

CodePudding user response:

With a1 as (select to_number (min (substr (a., 2, 4))) minb, to_number (Max (substr (a., 2, 4))) maxb from a),
A2 as (select the 'S' | | lpad (min_b + level 1) all_b from a1 connect by level<=max_b)
Select alln from a2 where not the exists (select 1 from a where clause a.=a2. All_b) order by 1;

CodePudding user response:

T the first line write wrong maxb, correcting for max_b
With a1 as (select to_number (min (substr (a., 2, 4))) minb, to_number (Max (substr (a., 2, 4))) max_b from a),
A2 as (select the 'S' | | lpad (min_b + level 1) all_b from a1 connect by level<=max_b)
Select alln from a2 where not the exists (select 1 from a where clause a.=a2. All_b) order by 1;

CodePudding user response:

SELECT the REPLACE (' S '| | TO_CHAR (ROWNUM,' 000 '), ' ', ') AS B
The FROM DUAL
CONNECT BY LEVEL & lt;=(SELECT TO_NUMBER (MAX (SUBSTR (a., 2))) FROM A)
MINUS
SELECT the FROM B A