Ich habe eine Tabelle mit einer Spalte für ID-Nummern, die nicht in einzelnen Schritten erhöht werden.

Es gibt also Zahlen, die nicht verwendet werden und die ich brauche. Die Spalte ist eine VarChar-Spalte.

Beispielsweise:

Verwendete Zahlen in der Tabelle = 2, 5, 7, 9, 10 usw.

Also brauche ich eine Abfrage, die mir = 1, 3, 4, 6, 8 usw. gibt.

Pseudo-Code so etwas wie:

Zahlen aus Tabelle NOT IN auswählen (Zahlen aus Tabelle auswählen)!

Ich habe es mit NOT IN und NOT EXISTS versucht, aber nichts funktioniert.

Kann mir jemand helfen, dies zu erreichen?

BEARBEITEN: Der Zahlenbereich reicht von 0 bis 99999999 !!!

1
comidos 19 Feb. 2020 im 18:16

3 Antworten

Beste Antwort
DECLARE @Table AS TABLE
(
    Id VARCHAR(5)
)

INSERT INTO @Table
VALUES
 ('1')
,('3')
,('5')
,('7')
,('10')

DECLARE @Range AS TABLE
(
    RangeId VARCHAR(10)
)

INSERT INTO @Range
SELECT TOP (1000000) n = CONVERT(VARCHAR(10), ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1)

select 
    MissingId = RangeId
from 
    @Range AS R
    LEFT OUTER JOIN @Table AS T ON T.Id = R.RangeId
WHERE
    CONVERT(INT,R.RangeId) <= (SELECT MAX(CONVERT(INT,Id)) FROM @Table)
    AND T.Id IS NULL
order by MissingId
1
Akash Patel 19 Feb. 2020 im 16:11

Wie Sie nicht erwähnen, ist die Obergrenze und rekursive allgemeine Tabellenausdrücke sind von Natur aus langsam, wären Sie mit einer Tally wahrscheinlich besser dran, um dies zu erreichen:

CREATE TABLE dbo.YourTable (ID int);

INSERT INTO dbo.YourTable (ID)
VALUES(1),(3),(5),(7),(9),(11),(13),(15),(216); --Big jump on purpose

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (SELECT MAX(ID) FROM dbo.YourTable) --Limit the tally for performance
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3) --1000 rows, add more Ns for more rows
SELECT I AS ID
FROM Tally T
     LEFT JOIN dbo.YourTable YT ON T.I = YT.ID
WHERE YT.ID IS NULL;

: Basierend auf dem Kommentar zu einer anderen Antwort:

Dies ist die richtige Richtung. Wenn ich es in meiner Situation mache, bekomme ich nur 100 Zahlen. Aber die Zahlen haben 8 Ziffern und es gibt VIEL mehr als 100 Zahlen!

8 Ziffern bedeuten, dass Sie IDs mit einem Wert von 10.000.000 (10 Millionen) plus haben. Das Erstellen von mehr als 10 Millionen Zeilen in einer Liste ist sehr E / A-intensiv. Ich empfehle dringend, dies in Chargen zu unterteilen.

Edit2: Ok, das Maximum (aus einem Kommentar zu dieser Antwort) beträgt 99.999.999! Dies sind Informationen, die definitiv in Frage kommen sollten. Dieser Prozess muss gestapelt werden, sonst werden Ihre Transaktionsprotokolle beendet.

1
Larnu 19 Feb. 2020 im 16:16

Ohne CTE können Sie den Trick master.dbo.spt_values verwenden. Ich bin mir nicht sicher, wozu diese Tabelle in msdb dient, aber sie enthält die Werte, die wir benötigen. Versuche es. Wenn Sie größere Werte als spt_values haben, teilen Sie Ihre maximale ID durch die maximale von spt_value und ersetzen Sie Nummer + 1 durch Nummer + 1 + (@ currentbatch * @ maxsptvalues) (zuerst) Charge ist Charge 0). Ich habe es weder getestet noch Code dafür geschrieben, aber so etwas sollte auf jeden Fall funktionieren. Sie können dies beispielsweise in einer while-Schleife tun.

IF OBJECT_ID('tmptbl') IS NOT null
DROP TABLE tmptbl
GO
SELECT * INTO tmptbl 
FROM 
(
SELECT '1' [id]
UNION 
SELECT '3'
UNION 
SELECT '5' ) t


DECLARE @maxid INT = 0
SELECT @maxid = MAX(id) FROM tmptbl

SELECT number+1
FROM master.dbo.spt_values
WHERE number < @maxid
AND Type = 'p'    
AND NOT EXISTS ( SELECT 1
                    FROM dbo.tmptbl
                    WHERE CONVERT(INT,[id]) = (number+1))
ORDER BY number

Das Ergebnis: 2,4

1
Cogent 19 Feb. 2020 im 19:49