SQL查询一个表中某个字段值第一个不连续的记录

问题描述：

假设有一张表 A，有字段 id，查询A表的id ，先对id去重再从小到大排序，再找出第一个不连续的id，也就是后一个id是前一个id+1，找到最后一个id还未找到则返回最后一个id
例如 1,2,3,4,6,7,8,9 的第一个不连续id是4

答

如果只想找出第一个不连续的id,不需要排序

select min(a.id) from A a
left join A b
on a.id = b.id -1
where b.id is null

答

select top 1 AID from (
select A0.ID as AID,A1.ID as BID from
(SELECT distinct [ID] FROM [A]) A0
left join (SELECT distinct [ID] FROM [A]) A1 on A0.ID+1=A1.ID
) t where BID is null order by AID asc

答

ids为id的集合
int i = min(ids);
int len = ids.length;
int _id = 0;
for v1 as select id from A order by id do
if id <> i then
_id = id -1;
break;
end if;
end for;

答

你把表和字段换一下就是
排序select distinct c_score from t_test order by c_score

不连续的
select case when c_score != (select distinct c_score from t_test order by c_score limit 1) then c_score
else (select distinct c_score from t_test order by c_score desc limit 1) end as c_score
from(
select c_score
from (select distinct c_score from t_test order by c_score) t
where not exists (select 1 from t_test where c_score = t.c_score-1) order by c_score desc limit 1
)a

答

mysql 了解下，因为ID不会重复，所以没去重，去重还耗费性能。

 SELECT tab.id FROM tab AS tab
 WHERE tab.id +1 NOT IN (SELECT id FROM tab )
 AND tab.id != (SELECT MAX(ID) FROM tab)
 ORDER BY tab.id ASC

答

如果是1238的ID查询出哪个ID这个需要根据你具体场景来调整sql

SQL查询一个表中某个字段值第一个不连续的记录

相关推荐