sql server 2005 - query - Consulta SQL para encontrar números de secuencia faltantes

para que sirve where en sql

Question

Tengo una columna llamada sequence . Los datos en esta columna se ven como 1, 2, 3, 4, 5, 7, 9, 10, 15.

Necesito encontrar los números de secuencia faltantes de la tabla. ¿Qué consulta SQL encontrará los números de secuencia faltantes de mi tabla? Estoy esperando resultados como

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

Estoy usando solo una mesa Probé la consulta a continuación, pero no obtengo los resultados que quiero.

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

¿No todas las soluciones dadas son demasiado complejas? no sería esto más simple:

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 2

¿Qué tal algo así como:

  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)

dando resultados resumidos:

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

Answer 3

Aquí hay una secuencia de comandos para crear un procedimiento almacenado que devuelve números secuenciales faltantes para un rango de fechas determinado.

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

IR

Answer 4

Crea una tabla de Tally útil:

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

Indíquelo o conviértalo en una sola columna como PK. Luego use EXCEPT para obtener su número faltante.

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

Answer 5

Esta es mi interpretación de este problema, colocando los contenidos en una variable de tabla a la que puedo acceder fácilmente en el resto de mi script.

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 6

Hay una discusión de SQL para resolver este tipo de problema en http://www.duelec.de/blog/?p=337 .

No está escrito específicamente en sqlserver2005, pero debería proporcionarle información suficiente para que lo adapte.

Answer 7

Las mejores soluciones son aquellas que usan una tabla temporal con la secuencia. Suponiendo que construya una tabla de este tipo, LEFT JOIN con verificación NULL debería hacer el trabajo:

SELECT      #sequence.value
FROM        #sequence
LEFT JOIN   MyTable ON #sequence.value = MyTable.value
WHERE       MyTable.value IS NULL

Pero si tiene que repetir esta operación a menudo (y más de una secuencia en la base de datos), crearía una tabla de "datos estáticos" y tendría una secuencia de comandos para llenarla con el MAX (valor) de todas las tablas que necesita .

Answer 8

Prueba con esto:

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 9

Sé que esta es una publicación muy antigua, pero quería agregar esta solución que encontré HERE para que pueda encontrarla más fácil:

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 10

Solo por diversión, decidí publicar mi solución.
Tenía una columna de identidad en mi mesa y quería encontrar los números de factura faltantes. Revisé todos los ejemplos que pude encontrar, pero no fueron lo suficientemente elegantes.

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 11

También puedes resolver usando algo así como un CTE para generar la secuencia completa:

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 - ¿el formateo no está funcionando aquí por alguna razón? ¿Alguien puede ver el problema?

Answer 12

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

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

Encontré esta respuesta aquí: http://sql-developers.blogspot.com/2012/10/how-to-find-missing-identitysequence.html

Estaba buscando una solución y encontré muchas respuestas. Este es el que usé y funcionó muy bien. Espero que esto ayude a cualquiera que busque una respuesta similar.

Answer 14

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 15

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;