T-SQL - 接收两个数字之间的差距 [英] T-SQL - Receive the gap between 2 Numbers

问题描述

我使用的是 SQL Server 2012.

I'm using SQL Server 2012.

我收到了最大数量"f.e.201900005 这告诉我范围从 201900000 开始(这是给定的).现在我想接收这个范围内缺少的数字.

I'm receiving a "Max Number" f.e. 201900005 this tells me the range starts at 201900000 (this is given). Now i want to receive the numbers which are missing in this range.

我已经查看了几个关于此的问题，但我似乎无法让它工作.根据自身检查表，使用 between 或使用游标.

I've looked at several Questions about this, but i can't seem to get it working. Checking the table against itself, using between or using a cursor.

Max Number = 201900005, Min Number = 201900000

test_table
+----------------+
|   test_number  |
+----------------+
|   201900001    |
|   201900003    |
|   201900004    |
+----------------+

result
+----------------+
|    missing     |
+----------------+
|   201900000    |
|   201900002    |
|   201900005    |
+----------------+

当前过程使用帮助"表，该表基本上包含 201900000 和 201900005 之间的所有数字(在实际情况中更多)，并将它们与 test_table 中的一次进行比较.

The current process works with a "helping"-table which essentally contains all numbers between 201900000 and 201900005 (a lot more in the real situation) and compares those to the onces in the test_table.

如果有任何建议，我将不胜感激.

I would be grateful for any suggestions.

推荐答案

就我个人而言，我会使用 Tally 创建一个包含所有可能数字的列表，然后 LEFT JOIN 将该列表添加到您的表并返回没有匹配的行:

Personally, I would would use a Tally to create a list of all the possible numbers, and then LEFT JOIN that list to your table and return rows where there is no match:

CREATE TABLE dbo.Test_Table (Test_Number int);
INSERT INTO dbo.Test_Table (Test_Number)
VALUES(201900001),(201900003),(201900004);
GO

DECLARE @Start int = 201900000,
        @End int = 201900005;

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (@End - @Start +1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 + @Start AS I
    FROM N N1, N N2, N N3) --1000 rows, if you need more, just add more
SELECT T.I
FROM Tally T
     LEFT JOIN dbo.Test_Table TT ON T.I = TT.Test_Number
WHERE TT.Test_Number IS NULL;

GO

DROP TABLE dbo.Test_Table;

DB<>小提琴

这篇关于T-SQL - 接收两个数字之间的差距的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！