葱葱那年丶
2018-09-05 06:43
采纳率: 81.3%
浏览 1.5k

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

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

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

6条回答 默认 最新

  • huajian_li 2018-09-05 08:55
    已采纳

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

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

    已采纳该答案
    打赏 评论
  • 智_慧 2018-09-05 07:14

    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

    打赏 评论
  • reboot_fk 2018-09-05 07:17

    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;

    打赏 评论
  • 爱_LOVE 2018-09-05 07:24

    你把表和字段换一下就是
    排序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

    打赏 评论
  • 路漫漫兮其修远兮 2018-09-05 09:09

    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
    
    打赏 评论
  • 路漫漫兮其修远兮 2018-09-05 09:09

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

    打赏 评论

相关推荐 更多相似问题