mysql 顺序列找出断号_SQL顺序列找出断号

select id from info

id

-----------

1

2

3

5

6

7

8

10

11

12

15

(11 行受影响)

方法一：

select (select max(id)+1 from Info where id

from Info a

where

a.id>(select max(id)+1 from Info where id

beginId     endId

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

4           4

9           9

13         14

(3 行受影响)

方法二：

select beginId,(select min(id)-1 from info where id > beginId) as endId

from (

select id+1 as beginId from info where id+1 not in (select id from info) and id < (select max(id) from info)

) as t

beginId     endId

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

4           4

9           9

13          14

(3 行受影响)

说明：

1、查找结果的两列是断号的区间，如果beginId=endId，则表示缺少该号码，否则表示缺少beginId ~ endId；

2、如果号码1不存在，区间1 ~ select min(id)-1 from info 将无法找出