表有1490行记录（1到1500），如何把没有的那10个id找出来？

Question

oracle数据库

Answer 1

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);

Answer 2

打印出来的就是不存在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;

Answer 3

查出不存在的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

Answer 4

另外一个思路：使用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 就是缺的。

Answer 5

使用minus更好吧
select rownum from dual connect by rownum<=1500
minus
select id from T

Answer 6

版主正解，我觉得还是使用minus比较好

Answer 7

minus 比较好，清晰，简单。

Answer 8

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);
这个也不错