select id from table 如何找出不连续的id..

最新推荐文章于 2023-05-14 13:06:54 发布

XiGua666666888888

最新推荐文章于 2023-05-14 13:06:54 发布

阅读量730

点赞数

分类专栏： oracle 文章标签： oracle Oracle ORACLE sql SQL

oracle 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

方法一：

SQL> select * from test;

      ID
----------
      3
      6
      7
      9
      12

SQL> declare
  2 v_min number := 0;
  3 v_max number := 0;
  4 v_count number := 0;
  5 v_value number := 0;
  6
  7 cursor csr_test is
  8    select id from test order by id;
  9
10  begin
11 select min(id) into v_min from test;
12 select max(id) into v_max from test;
13 if (v_min < v_max-1) then
14    v_count := v_min;
15
16    open csr_test;
17    fetch csr_test into v_value;
18
19    loop
20       fetch csr_test into v_value;
21       exit when csr_test%notfound;
22       while (v_count < v_value-1) loop
23       v_count := v_count + 1;
24       dbms_output.put_line(v_count);
25       end loop;
26       v_count := v_value;
27    end loop;
28    close csr_test;
29 end if;
30  end;

31 /

方法二：

SELECT ROWNUM FROM ALL_OBJECTS WHERE ROWNUM < = (SELECT MAX(ID) FROM YOURTABLE)
MINUS
SELECT ID FROM YOURTABLE
;