查找缺失序列号的 SQL 查询 [英] SQL query to find Missing sequence numbers
本文介绍了查找缺失序列号的 SQL 查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一列名为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屋!
查看全文