SQL查询以查找缺少的序列号 [英] SQL query to find Missing sequence numbers

问题描述

我有一列名为sequence.此列中的数据类似于1，2，3，4，5，7，9，10，15

I have a column named sequence. The data in this column looks like 1, 2, 3, 4, 5, 7, 9, 10, 15.

我需要从表中找到丢失的序列号.哪些SQL查询会从我的表中找到丢失的序列号?我希望得到类似

I need to find the missing sequence numbers from the table. What SQL query will find the missing sequence numbers from my table? I am expecting results like

Missing numbers
---------------
6  
8  
11  
12  
13  
14  

我只使用一张桌子.我在下面尝试查询，但没有得到想要的结果.

I am using only one table. I tried the query below, but am not getting the results I want.

select de.sequence + 1 as sequence from dataentry as de 
left outer join dataentry as de1 on de.sequence + 1 = de1.sequence
where de1.sequence is null  order by sequence asc;

推荐答案

诸如此类的事情

  select (select isnull(max(val)+1,1) from mydata where val < md.val) as [from],
     md.val - 1 as [to]
  from mydata md
  where md.val != 1 and not exists (
        select 1 from mydata md2 where md2.val = md.val - 1)

给出汇总结果:

from        to
----------- -----------
6           6
8           8
11          14

这篇关于SQL查询以查找缺少的序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！