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

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

sql

6个回答

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

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

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
weixin_38799368
内蒙刘德华 很溜
接近 2 年之前 回复

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

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

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问