SERIALNUMBER
33
11
4
5
36
37
2以上为SERIALNUMBER这个字段的数据。
给一个范围,例如1-50,想求出中间缺少的号,缺号:1、3、6、7、8........
只剩35分了,分少情意重嘛,再次感谢各位了。Oraclesql for 缺号Oracleforsql缺号分析
33
11
4
5
36
37
2以上为SERIALNUMBER这个字段的数据。
给一个范围,例如1-50,想求出中间缺少的号,缺号:1、3、6、7、8........
只剩35分了,分少情意重嘛,再次感谢各位了。Oraclesql for 缺号Oracleforsql缺号分析
with t(SERIALNUMBER) as
(
select 33 from dual
union all select 11 from dual
union all select 4 from dual
union all select 5 from dual
union all select 36 from dual
union all select 37 from dual
union all select 2 from dual
)
select r from (select rownum r from dual connect by rownum<51) t1
where not exists(select 1 from t where t.SERIALNUMBER=t1.r)
order by r;
--如果不是範圍從1開始,在where後加限定r的範圍,t1限定到最大就可以了
DECLARE
o_s NUMBER;
BEGIN
FOR i IN 1..50 LOOP
select COUNT(*) INTO o_s from test_20130115 WHERE serialno = i;
IF o_s =0 THEN
dbms_output.put_line(i);
END IF;
END LOOP;
END;
BTW,我测试了一下两个语句的查询效率,如果查询1到500之间的话,用connect by 比 for 循环要慢很多很多
with t as
(
select 33 as SERIALNUMBER from dual
union all select 11 from dual
union all select 4 from dual
union all select 5 from dual
union all select 36 from dual
union all select 37 from dual
union all select 2 from dual
)
select r from (select rownum r from dual connect by rownum<51) t1
where not exists(select 1 from t where t.SERIALNUMBER=t1.r)
order by r;