a表中有欄位b,其值都是以S開頭的如S001,S002,S004,S006,...等，我現需要將其中未出現過的S003，S005,...等資料SELECT出來，這樣的SQL怎么寫呢，求大神幫助
uj5u.com熱心網友回復：
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;
uj5u.com熱心網友回復：
t第一行寫錯了maxb,糾正為max_bwith 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;
uj5u.com熱心網友回復：
SELECT REPLACE('S' || TO_CHAR(ROWNUM, '000'), ' ', '') AS BFROM DUAL
CONNECT BY LEVEL <= (SELECT TO_NUMBER(MAX(SUBSTR(A.B, 2))) FROM A)
MINUS
SELECT B FROM A
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/83817.html
標籤：非技術區
上一篇：sql陳述句客房狀態
下一篇：HQL 多個關鍵字查詢一個欄位