SQL判断字段值是否连续与是否从1开始

发表于 更新于 阅读次数：

最近遇到一个运维问题，编写SQL语句判断某个字段是否从1开始，是否是连续的，记录一下问题解决过程。

目标数据表结构

目标数据表 Department 结构如下：

departId personId
7523 7523001
7523 7523002
7523 7523003
7524 7524001
7524 7524002
7524 7524003

判断字段是否从1开始

1
2
select * from Department a 
where a.personId in ( select * from ( select min(personId) as personId from Department b group by b.departId )) and mod(personId, 1000) !=1

判断字段是否连续

1
2
3
4
5
6
7
select * from 
( select b.personId,
( select max(personId) 
from Department a 
where a.personId < b.personId and a.departId = b.departId ) as prePersonId 
from Department b )
where personId - 1 > prePersonId

参考连接

  1. 在论坛中出现的比较难的sql问题：7(子查询 判断某个字段的值是否连续),by LongRui888.