blocks|key|941525|text|我会在同一个表中执行子查询，以查看当前数字-1是否存在另一个数字，如果没有，您就知道跳过了一个数字。你也可以做这个的%2B1。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|941526|select+nt.numb，CASE+(select+COUNT(*)+from+table+where+numb=nt.numb-1)=0然后从numbertable中‘跳过’否则‘不跳过’|941527|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I would do a subquery in the same table, to see if another number exist for the current number-1, and if there is not one, you know that a number was skipped. You can do the +1 of this as well.

select 
 nt.numb,
 CASE 
 (select COUNT(*) from table where numb=nt.numb-1)=0 THEN 'skipped' ELSE 'not skipped'
from
 numbertable nt

blocks|key|3008923|text|这将适用于数字0-+2000对于较大的数字，您只需交叉连接原始结果集。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3008924|with+temp+as+(
++select+distinct+number
from+master..spt_Values
where+number+between+0+and+2000
)

select+*+from
temp+t
left+join+your_table+y+on+y.col1+=+t.number
where+y.col1+is+null+|code-block|syntax|javascript|3008925|或者使用交叉连接|3008926|这将对数十亿人起作用，显然要慢一些。|3008927|++++WITH
++++++L0+++AS(SELECT+1+AS+c+UNION+ALL+SELECT+1),
++++++L1+++AS(SELECT+1+AS+c+FROM+L0+AS+A+CROSS+JOIN+L0+AS+B),
++++++L2+++AS(SELECT+1+AS+c+FROM+L1+AS+A+CROSS+JOIN+L1+AS+B),
++++++L3+++AS(SELECT+1+AS+c+FROM+L2+AS+A+CROSS+JOIN+L2+AS+B),
++++++L4+++AS(SELECT+1+AS+c+FROM+L3+AS+A+CROSS+JOIN+L3+AS+B),
++++++L5+++AS(SELECT+1+AS+c+FROM+L4+AS+A+CROSS+JOIN+L4+AS+B),
++++++Nums+AS(SELECT+ROW_NUMBER()+OVER(ORDER+BY+(SELECT+NULL))+AS+n+FROM+L5)

++select+*+from
++++l5+t
++++left+join+your_table+y+on+y.col1+=+t.n
++++where+y.col1+is+null+|3008928|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

This will work for numbers 0 - 2000 for large numbers you just need to cross join the original result set.

<pre><code>with temp as (
 select distinct number
from master..spt_Values
where number between 0 and 2000
)

select * from
temp t
left join your_table y on y.col1 = t.number
where y.col1 is null 
</code></pre>

alternatively using cross join

This will work for billions obviously slower

<pre><code> WITH
 L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
 L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
 L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
 L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
 L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
 L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
 Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)

 select * from
 l5 t
 left join your_table y on y.col1 = t.n
 where y.col1 is null 
</code></pre>

blocks|key|693235|text|这看起来很像是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|693236|SQL+query+to+find+Missing+sequence+numbers|offset|length|693237|有一个建议，这将是可行的：|693238|SELECT++++++l.id+%2B+1+as+start
FROM++++++++Table1+as+l
LEFT+JOIN+++Table1+as+r+on+l.id+%2B+1+=+r.id
WHERE+++++++r.id+IS+NULL|code-block|syntax|javascript|693239|否则，您可以在表上使用顺序表进行连接。从上面的问题中，您可以查看http://www.projectdmx.com/tsql/tblnumbers.aspx来了解如何生成合适的顺序表，连接将类似于|693240|SELECT++++++#sequence.value
FROM++++++++#sequence
LEFT+JOIN+++Table1+ON+#sequence.value+=+Table1.value
WHERE+++++++Table1.value+IS+NULL|693241|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/1057389/sql-query-to-find-missing-sequence-numbers|1|http://www.projectdmx.com/tsql/tblnumbers.aspx^0|0|0|16|0|0|0|0|W|1A|1|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@$D|12|E|13|1|14]]|A|$]]|$1|F|3|G|5|6|7|15|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|16|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|17|8|@]|9|@$D|18|E|19|1|1A]]|A|$]]|$1|O|3|P|5|J|7|1B|8|@]|9|@]|A|$K|L]]|$1|Q|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]|Y|$5|T|U|V|A|$W|Z]]]]

This seems to pretty much be a duplication of

<a href="https://stackoverflow.com/questions/1057389/sql-query-to-find-missing-sequence-numbers">SQL query to find Missing sequence numbers</a>

There's a suggestions this will work:

<pre><code>SELECT l.id + 1 as start
FROM Table1 as l
LEFT JOIN Table1 as r on l.id + 1 = r.id
WHERE r.id IS NULL
</code></pre>

Otherwise you can left join on your table with a sequence table. From the above question, you can look at <a href="http://www.projectdmx.com/tsql/tblnumbers.aspx" rel="nofollow noreferrer">http://www.projectdmx.com/tsql/tblnumbers.aspx</a> to get some ideas on how to generate a suitable sequence table, and the join will be something like

<pre><code>SELECT #sequence.value
FROM #sequence
LEFT JOIN Table1 ON #sequence.value = Table1.value
WHERE Table1.value IS NULL
</code></pre>

blocks|key|693247|text|对于所有递归建议，请注意。递归CTE增加时间与行数成线性关系。使用计数表或交叉连接要好得多……|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|693248|这将会起作用：|693249|--+data+table
CREATE+TABLE+#data+(
++++value+INT
)

INSERT+#data+VALUES+(1)
INSERT+#data+VALUES+(2)
INSERT+#data+VALUES+(3)
INSERT+#data+VALUES+(4)
INSERT+#data+VALUES+(6)
INSERT+#data+VALUES+(7)
INSERT+#data+VALUES+(8)
INSERT+#data+VALUES+(9)
INSERT+#data+VALUES+(10)
INSERT+#data+VALUES+(13)
INSERT+#data+VALUES+(14)

--+normally+i+have+a+tally+table+already+for+stuff+like+this+but+I'll
--+create+one+temporary+here.
CREATE+TABLE+#tmp_tally+(
++++n+INT
)

DECLARE+@n+INT
SET+@n+=+1
WHILE+@n+<+14
BEGIN
++++INSERT+#tmp_tally+VALUES+(@n)
++++SET+@n+=+@n+%2B+1
END

SELECT
++++T.n,
++++CASE+WHEN+#data.value+IS+NULL+THEN+'Missing'+ELSE+'Not+Missing'+END
FROM
++++#tmp_tally+T
++++LEFT+JOIN+#data+ON
++++T.n+=+#data.value
WHERE
++++T.n+<=+(SELECT+MAX(value)+FROM+#data)+--+max+of+what+you+want+to+check+against+which+is+14+in+your+example

DROP+TABLE+#data
DROP+TABLE+#tmp_tally|code-block|syntax|javascript|693250|entityMap^0|6|6|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

Side-note to all recursive CTE suggestions. The recursive CTE increases time linear to the number of rows. Using a tally table or cross-join is much better to use...

This would work:

<pre><code>-- data table
CREATE TABLE #data (
 value INT
)

INSERT #data VALUES (1)
INSERT #data VALUES (2)
INSERT #data VALUES (3)
INSERT #data VALUES (4)
INSERT #data VALUES (6)
INSERT #data VALUES (7)
INSERT #data VALUES (8)
INSERT #data VALUES (9)
INSERT #data VALUES (10)
INSERT #data VALUES (13)
INSERT #data VALUES (14)

-- normally i have a tally table already for stuff like this but I'll
-- create one temporary here.
CREATE TABLE #tmp_tally (
 n INT
)

DECLARE @n INT
SET @n = 1
WHILE @n &lt; 14
BEGIN
 INSERT #tmp_tally VALUES (@n)
 SET @n = @n + 1
END

SELECT
 T.n,
 CASE WHEN #data.value IS NULL THEN 'Missing' ELSE 'Not Missing' END
FROM
 #tmp_tally T
 LEFT JOIN #data ON
 T.n = #data.value
WHERE
 T.n &lt;= (SELECT MAX(value) FROM #data) -- max of what you want to check against which is 14 in your example

DROP TABLE #data
DROP TABLE #tmp_tally
</code></pre>

blocks|key|3008975|text|正如在其他答案中所说的，最好的选择是与真实的序列表进行连接。您可以使用递归CTE创建一个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3008976|DECLARE+@MaxNumber+INT
SELECT+@MaxNumber+=+MAX(Col1)+FROM+YourTable;

WITH+CTE+AS
(
++++SELECT+1+Col1
++++UNION+ALL
++++SELECT+Col1%2B1
++++FROM+CTE+
++++WHERE+Col1%2B1+<=+@MaxNumber
)
SELECT+A.Col1
FROM+CTE+A
LEFT+JOIN+YourTable+B
ON+A.Col1+=+B.Col1
WHERE+B.Col1+IS+NULL
OPTION(MAXRECURSION+0)|code-block|syntax|javascript|3008977|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

As was said in other answers, the best choice is to do a join with a real sequence table. You can create one using a recursive CTE:

<pre><code>DECLARE @MaxNumber INT
SELECT @MaxNumber = MAX(Col1) FROM YourTable;

WITH CTE AS
(
 SELECT 1 Col1
 UNION ALL
 SELECT Col1+1
 FROM CTE 
 WHERE Col1+1 &lt;= @MaxNumber
)
SELECT A.Col1
FROM CTE A
LEFT JOIN YourTable B
ON A.Col1 = B.Col1
WHERE B.Col1 IS NULL
OPTION(MAXRECURSION 0)
</code></pre>

blocks|key|3008990|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3008991|declare+@min+int
declare+@max+int

select+@min+=+min(field_ID),+@max+=+max(field_ID)+from+[Table]

create+table+#tmp+(Field_No+int)
while+@min+<=+@max
begin
+++if+not+exists+(select+*+from+[Table]+where+field_ID+=+@min)
++++++insert+into+#tmp+(seq_field)+values+(@min)
+++set+@min+=+@min+%2B+1
end
select+*+from+#tmp
drop+table+#tmp|code-block|syntax|javascript|3008992|使用上面的脚本，您将在#tmp表的"ID“列中获得缺少的值。|offset|length|style|BOLD|3008993|希望这能对你有所帮助！|3008994|entityMap^0|0|0|I|2|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Try this: 

<pre><code>declare @min int
declare @max int

select @min = min(field_ID), @max = max(field_ID) from [Table]

create table #tmp (Field_No int)
while @min &lt;= @max
begin
 if not exists (select * from [Table] where field_ID = @min)
 insert into #tmp (seq_field) values (@min)
 set @min = @min + 1
end
select * from #tmp
drop table #tmp
</code></pre>

With the above script you will get missing values in "ID" column from #tmp table.

Hope this will help you!!

Table1 is as follows :

<pre><code>Col1
1
2
3
4
6
7
8
9
10
13
14
</code></pre>

As shown above the col1 has the sequence of values but for some reason the user did not insert 5, 11 and so on. How to find out the missing values in a sequence. Here the sequence is 1 to 14 and the missing values are 5,11. Please help me.

finding the missing values in a Sequence

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

Table1格式如下：Col112346789101314如上所示，col1具有值序列，但是由于某种原因，用户没有插入5、11等等。如何找出序列中的缺失值。这里的序列是1到14，缺失值是5,11。请帮助我。

问查找序列中的缺失值
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问查找序列中的缺失值EN