oracle 查询只查询数字,一个查询不连续数字记录的问题

给你一个好理解的！

具体如下：

select  min(max(sr_no)) + 1 sr_no

from

(select sr_no,

sr_no - row_number() over(order by sr_no) diff

from tb_a

where sr_no between &start_num and &end_num)

group by diff;

----------------------

--Testing

SQL> select * from tb_a;

SR_NO

----------

1

2

3

5

6

7

9

11

12

9 rows selected

SQL>

SQL> select  min(max(sr_no)) + 1 sr_no

2  from

3  (select sr_no,

4          sr_no - row_number() over(order by sr_no) diff

5    from tb_a

6    where sr_no between 1 and 12)

7  group by diff;

SR_NO

----------

4

SQL>

SQL> select  min(max(sr_no)) + 1 sr_no

2  from

3  (select sr_no,

4          sr_no - row_number() over(order by sr_no) diff

5    from tb_a

6    where sr_no between 5 and 12)

7  group by diff;

SR_NO

----------

8

SQL>

[本帖最后由 bell6248 于 2010-1-11 16:51 编辑]