oracle数据库的表中有1490行记录，id从1到1500，怎样把没有的那10个id找出来？
------解决思路----------------------
select t1.rn
from (select level rn from dual connect by level < 1501) t1
where not exists (select 1 from table t2 where t1.rn = t2.id);
------解决思路----------------------
打印出来的就是不存在ID
declare
i number;
nums number;
begin
for i in 1..1500 loop
select count(*) into nums from address_list where id = i;
if nums = 0 then
dbms_output.put_line(i);
end if;
end loop;
end;
------解决思路----------------------
查出不存在的id，sql 实现：
select a.rn from (select rownum rn from dual connect by rownum < =1500) a
where not exists(select 1 from 表名 b where a.rn=b.id) order by a.rn
------解决思路----------------------
另外一个思路：使用lead分析函数
scott@PROD>CREATE TABLE t_no (cno NUMBER(4));
Table created.
scott@PROD>DECLARE
2 I INTEGER;
3 BEGIN
4 FOR I IN 1 .. 1500
5 LOOP
6 INSERT INTO T_NO VALUES (I);
7 END LOOP;
8 COMMIT;
9 END;
10 /
PL/SQL procedure successfully completed.
scott@PROD>DELETE FROM t_no WHERE cno IN (6, 100, 101, 1000, 1001, 1200, 1400, 1403, 1480, 1481);
10 rows deleted.
scott@PROD>commit;
Commit complete.
scott@PROD>SELECT CNO, NEXT_CNO
2 FROM (SELECT CNO, LEAD(CNO, 1) OVER(ORDER BY CNO) AS NEXT_CNO FROM T_NO) T
3 WHERE T.NEXT_CNO - T.CNO > 1;
CNO NEXT_CNO
---------- ----------
5 7
99 102
999 1002
1199 1201
1399 1401
1402 1404
1479 1482
7 rows selected.
查询出来的行次，NEXT_CNO - CNO 就是缺的。
------解决思路----------------------
使用minus更好吧
select rownum from dual connect by rownum<=1500
minus
select id from T