我有一个名为列sequence 。 在此列中的数据的模样1，2，3，4，5，7，9，10，15。
我需要找到从表中丢失的序列号。 什么SQL查询会找到我的表中丢失的序列号？ 我期待类似的结果
Missing numbers
---------------
6
8
11
12
13
14
我只使用一个表。 我想下面的查询，但我没有得到我想要的结果。
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
我知道这是一个很老的文章，但我想添加这个解决方案，我发现这里让我能找到更容易：
WITH Missing (missnum, maxid)
AS
(
SELECT 1 AS missnum, (select max(id) from @TT)
UNION ALL
SELECT missnum + 1, maxid FROM Missing
WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN @TT tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0);
试试这个：
declare @min int
declare @max int
select @min = min(seq_field), @max = max(seq_field) from [Table]
create table #tmp (Field_No int)
while @min <= @max
begin
if not exists (select * from [Table] where seq_field = @min)
insert into #tmp (Field_No) values (@min)
set @min = @min + 1
end
select * from #tmp
drop table #tmp
最佳的解决方案是那些使用临时表与序列。 假设你建立这样一个表，LEFT JOIN用NULL检查应该做的工作：
SELECT #sequence.value
FROM #sequence
LEFT JOIN MyTable ON #sequence.value = MyTable.value
WHERE MyTable.value IS NULL
但是，如果你要经常重复此操作（更然后对数据库中的1个序列），我想创建一个“静态数据”表，并有一个脚本给它填充到您所需要的所有表的MAX（值） 。
SELECT CASE WHEN MAX(column_name) = COUNT(*)
THEN CAST(NULL AS INTEGER)
-- THEN MAX(column_name) + 1 as other option
WHEN MIN(column_name) > 1
THEN 1
WHEN MAX(column_name) <> COUNT(*)
THEN (SELECT MIN(column_name)+1
FROM table_name
WHERE (column_name+ 1)
NOT IN (SELECT column_name FROM table_name))
ELSE NULL END
FROM table_name;
下面是一个脚本来创建一个存储过程，对于一个给定的日期范围返回缺少的序列号。
CREATE PROCEDURE dbo.ddc_RolledBackOrders
-- Add the parameters for the stored procedure here
@StartDate DATETIME ,
@EndDate DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Min BIGINT
DECLARE @Max BIGINT
DECLARE @i BIGINT
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
BEGIN
DROP TABLE #TempTable
END
CREATE TABLE #TempTable
(
TempOrderNumber BIGINT
)
SELECT @Min = ( SELECT MIN(ordernumber)
FROM dbo.Orders WITH ( NOLOCK )
WHERE OrderDate BETWEEN @StartDate AND @EndDate)
SELECT @Max = ( SELECT MAX(ordernumber)
FROM dbo.Orders WITH ( NOLOCK )
WHERE OrderDate BETWEEN @StartDate AND @EndDate)
SELECT @i = @Min
WHILE @i <= @Max
BEGIN
INSERT INTO #TempTable
SELECT @i
SELECT @i = @i + 1
END
SELECT TempOrderNumber
FROM #TempTable
LEFT JOIN dbo.orders o WITH ( NOLOCK ) ON tempordernumber = o.OrderNumber
WHERE o.OrderNumber IS NULL
END
走
这是我这个问题的解释，把内容放在表变量，我可以在我的脚本的其余部分很容易地访问。
DECLARE @IDS TABLE (row int, ID int)
INSERT INTO @IDS
select ROW_NUMBER() OVER (ORDER BY x.[Referred_ID]), x.[Referred_ID] FROM
(SELECT b.[Referred_ID] + 1 [Referred_ID]
FROM [catalog].[dbo].[Referrals] b) as x
LEFT JOIN [catalog].[dbo].[Referrals] a ON x.[Referred_ID] = a.[Referred_ID]
WHERE a.[Referred_ID] IS NULL
select * from @IDS
只是为了好玩，我决定后我的解决方案。
我在我的表有标识列，我想寻找失踪的发票编号。 我查看了所有的例子，我能找到，但他们还不够优雅。
CREATE VIEW EENSkippedInvoicveNo
AS
SELECT CASE WHEN MSCNT = 1 THEN CAST(MSFIRST AS VARCHAR (8)) ELSE
CAST(MSFIRST AS VARCHAR (8)) + ' - ' + CAST(MSlAST AS VARCHAR (8)) END AS MISSING,
MSCNT, INV_DT FROM (
select invNo+1 as Msfirst, inv_no -1 as Mslast, inv_no - invno -1 as msCnt, dbo.fmtdt(Inv_dt) AS INV_dT
from (select inv_no as invNo, a4glidentity + 1 as a4glid
from oehdrhst_sql where inv_dt > 20140401) as s
inner Join oehdrhst_sql as h
on a4glid = a4glidentity
where inv_no - invno <> 1
) AS SS
DECLARE @MaxID INT = (SELECT MAX(timerecordid) FROM dbo.TimeRecord)
SELECT SeqID AS MissingSeqID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY column_id) SeqID from sys.columns) LkUp
LEFT JOIN dbo.TimeRecord t ON t.timeRecordId = LkUp.SeqID
WHERE t.timeRecordId is null and SeqID < @MaxID
我发现这个答案在这里： http://sql-developers.blogspot.com/2012/10/how-to-find-missing-identitysequence.html
我一直在寻找一个解决方案，发现了很多的答案。 这是我用了一个和它的工作非常出色。 我希望这可以帮助任何人寻找一个类似的答案。
-- This will return better Results
-- ----------------------------------
;With CTERange
As (
select (select isnull(max(ArchiveID)+1,1) from tblArchives where ArchiveID < md.ArchiveID) as [from],
md.ArchiveID - 1 as [to]
from tblArchives md
where md.ArchiveID != 1 and not exists (
select 1 from tblArchives md2 where md2.ArchiveID = md.ArchiveID - 1)
) SELECT [from], [to], ([to]-[from])+1 [total missing]
From CTERange
ORDER BY ([to]-[from])+1 DESC;
from to total missing
------- ------- --------------
6 6 1
8 8 1
11 14 4
是不是所有的给予解决方案太复杂？ 这是不是可以简单得多：
SELECT *
FROM (SELECT row_number() over(order by number) as N from master..spt_values) t
where N not in (select 1 as sequence union
select 2 union
select 3 union
select 4 union
select 5 union
select 7 union
select 10 union
select 15
)
DECLARE @TempSujith TABLE
(MissingId int)
Declare @Id Int
DECLARE @mycur CURSOR
SET @mycur = CURSOR FOR Select Id From tbl_Table
OPEN @mycur
FETCH NEXT FROM @mycur INTO @Id
Declare @index int
Set @index = 1
WHILE @@FETCH_STATUS = 0
BEGIN
if (@index < @Id)
begin
while @index < @Id
begin
insert into @TempSujith values (@index)
set @index = @index + 1
end
end
set @index = @index + 1
FETCH NEXT FROM @mycur INTO @Id
END
Select Id from tbl_Table
select MissingId from @TempSujith
创建一个有用的理货表 ：
-- can go up to 4 million or 2^22
select top 100000 identity(int, 1, 1) Id
into Tally
from master..spt_values
cross join master..spt_values
指数，或使单个列的PK。 然后使用除了让你丢失的数量。
select Id from Tally where Id <= (select max(Id) from TestTable)
except
select Id from TestTable
你也可以使用解决像CTE东西生成完整的序列：
create table #tmp(sequence int) insert into #tmp(sequence) values (1) insert into #tmp(sequence) values (2) insert into #tmp(sequence) values (3) insert into #tmp(sequence) values (5) insert into #tmp(sequence) values (6) insert into #tmp(sequence) values (8) insert into #tmp(sequence) values (10) insert into #tmp(sequence) values (11) insert into #tmp(sequence) values (14)
DECLARE @max INT
SELECT @max = max(sequence) from #tmp;
with full_sequence
(
Sequence
)
as
(
SELECT 1 Sequence
UNION ALL
SELECT Sequence + 1
FROM full_sequence
WHERE Sequence < @max
)
SELECT
full_sequence.sequence
FROM
full_sequence
LEFT JOIN
#tmp
ON
full_sequence.sequence = #tmp.sequence
WHERE
#tmp.sequence IS NULL
Hmmmm - 格式不工作就在这里出于某种原因？ 任何人都可以看到这个问题？
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://img.manongdao.com/sparkler-677774__340.jpg', '推荐 够拽才男人 的文章《SQL查询来寻找丢失的序列号SQL查询来寻找丢失的序列号(SQL query to find Mis》','https://www.manongdao.com/article-837828.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}