SQL查询查找丢失的序列号

Question

我有一个名为 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;

Answer 1

SELECT TOP 1 (Id + 1)
FROM CustomerNumberGenerator
WHERE (Id + 1) NOT IN ( SELECT Id FROM CustomerNumberGenerator )

为我的公司开发客户编号生成器。 不是最有效的，但绝对是最可读的

该表有一个 Id 列。
该表允许用户手动插入 ID 序列。
该解决方案解决了用户决定选择较大数字的情况

Answer 2

怎么样：

  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

Answer 3

我知道这是一篇非常旧的帖子，但我想添加我发现的这个解决方案 这里这样我可以更容易地找到它：

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); 

Answer 4

尝试用这个：

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

Answer 5

最好的解决方案是使用带有序列的临时表。 假设您构建这样一个表，带有 NULL 检查的 LEFT JOIN 应该可以完成这项工作：

    SELECT      #sequence.value
    FROM        #sequence
    LEFT JOIN   MyTable ON #sequence.value = MyTable.value
    WHERE       MyTable.value IS NULL
            AND #sequence.value < (SELECT MAX(MyTable.value) as max_value FROM MyTable)

但是如果您必须经常重复此操作（并且对于数据库中的 1 个序列更多），我将创建一个“静态数据”表并具有将其填充到您需要的所有表的 MAX（值）的脚本。

Answer 6

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;

Answer 7

下面是一个用于创建存储过程的脚本，该存储过程返回给定日期范围内缺失的序列号。

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

去

Answer 8

 -- 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

Answer 9

所有给出的解决方案都太复杂了吗？
这不是更简单吗：

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
        )

Answer 10

这是我对此问题的解释，将内容放置在一个表变量中，我可以在脚本的其余部分轻松访问该变量。

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

Answer 11

只是为了好玩，我决定发布我的解决方案。
我的表中有一个身份列，我想找到丢失的发票号码。
我回顾了我能找到的所有示例，但它们不够优雅。

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

Answer 12

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

我一直在寻找解决方案并找到了很多答案。 这是我用过的，效果非常好。 我希望这可以帮助任何寻找类似答案的人。

Answer 13

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

Answer 14

创建一个有用的Tally 表：

-- 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。
然后使用 EXCEPT 获取丢失的号码。

select Id from Tally where Id <= (select max(Id) from TestTable)
except
select Id from TestTable

Answer 15

您还可以使用 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

嗯 - 由于某种原因，格式在这里不起作用？ 任何人都可以看到问题吗？

Answer 16

我做了一个过程，这样你就可以发送表名和密钥，结果是给定表中缺失数字的列表

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[action_FindMissing_Autoincremnt]
(
@tblname as nvarchar(50),
@tblKey as nvarchar(50)
)
AS
BEGIN
    SET NOCOUNT ON;

declare @qry nvarchar(4000)



set @qry = 'declare @min int ' 
set @qry = @qry + 'declare @max int '

set @qry = @qry +'select @min = min(' + @tblKey + ')'
set @qry = @qry + ', @max = max('+ @tblKey +') '
set @qry = @qry + ' from '+ @tblname 

set @qry = @qry + ' create table #tmp (Field_No int)
while @min <= @max
begin
   if not exists (select * from '+ @tblname +' where '+ @tblKey +' = @min)
      insert into #tmp (Field_No) values (@min)
   set @min = @min + 1
end
select * from #tmp order by Field_No
drop table #tmp '

exec sp_executesql @qry 

END
GO

Answer 17

此查询生成一个范围从 1 到 15 的序列。（根据您所需的范围进行更改）

WITH AllNumbers AS (
  SELECT 1 AS number
  UNION ALL
  SELECT number + 1
  FROM dataentry
  WHERE number < 15
)

SELECT number
FROM AllNumbers
WHERE number NOT IN (SELECT number FROM dataentry)

通过选择 cte 表达式中没有的数字，您将获得缺失的数字。