建表如下
create table test(id number(4));
insert into test (id) values(1);
insert into test (id) values(2);
insert into test (id) values(4);
insert into test (id) values(5);
insert into test (id) values(6);
insert into test (id) values(8);
insert into test (id) values(9);
要怎么查詢其中缺失的號碼呢？
連續號應為1到9，但缺失了3和7
查詢結果應為3，7
uj5u.com熱心網友回復：
結果要號碼段，還是要明細？uj5u.com熱心網友回復：
只要號碼段，查出3，7就可以
謝謝幫忙
uj5u.com熱心網友回復：
with t1 as
(
select 1 id from dual union all
select 2 id from dual union all
select 4 id from dual union all
select 5 id from dual union all
select 6 id from dual union all
select 8 id from dual union all
select 9 id from dual
)
, t2 as
(
select level id2 from dual connect by level<=(select max(id) from t1)
)
select * from t2 where id2 not in (select id from t1)
uj5u.com熱心網友回復：
select b.rn from
test a,
(select rownum rn from dual connect by rownum <= 10) b
where b.rn = a.id(+)
and a.id is null
uj5u.com熱心網友回復：
-- 湊個人數
with t1 as
(
select 1 id from dual union all
select 2 id from dual union all
select 4 id from dual union all
select 5 id from dual union all
select 6 id from dual union all
select 8 id from dual union all
select 9 id from dual
)
select rownum from dual connect by rownum <=10
minus
select id from t1
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/102179.html
標籤：開發