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;