查找 SQL 中的所有整数间隙 [英] Find all integer gaps in SQL

问题描述

我有一个数据库，用于存储有关我从外部来源获取的游戏的不同比赛的信息.由于一些问题，数据库中偶尔会出现空白(从 1 个缺失的 ID 到几百个不等).我想让程序提取丢失游戏的数据，但我需要先获取该列表.

I have a database which is used to store information about different matches for a game that I pull in from an external source. Due to a few issues, there are occasional gaps (which could be anywhere from 1 missing ID to a few hundred) in the database. I want to have the program pull in the data for the missing games, but I need to get that list first.

表格格式如下:

id (pk-identity)  |  GameID (int)  |  etc.  |  etc.  

我曾想过编写一个程序来运行一个循环并查询每个从 1 开始的 GameID，但似乎应该有一种更有效的方法来获取缺失的数字.

I had thought of writing a program to run through a loop and query for each GameID starting at 1, but it seems like there should be a more efficient way to get the missing numbers.

是否有一种简单有效的方法，使用 SQL Server 来查找范围内所有缺失的数字?

Is there an easy and efficient way, using SQL Server, to find all the missing numbers from the range?

推荐答案

我们的想法是看看差距从哪里开始.让我假设您使用的是 SQL Server 2012，因此具有 lag() 和 lead() 函数.以下获取下一个 id:

The idea is to look at where the gaps start. Let me assume you are using SQL Server 2012, and so have the lag() and lead() functions. The following gets the next id:

select t.*, lead(id) over (order by id) as nextid
from t;

如果有间隙，则nextid <>id+1.您现在可以使用 where:

If there is a gap, then nextid <> id+1. You can now characterize the gaps using where:

select id+1 as FirstMissingId, nextid - 1 as LastMissingId
from (select t.*, lead(id) over (order by id) as nextid
      from t
     ) t
where nextid <> id+1;

如果没有 lead()，我会用相关子查询做同样的事情:

Without the lead(), I would do the same thing with a correlated subquery:

select id+1 as FirstMissingId, nextid - 1 as LastMissingId
from (select t.*,
             (select top 1 id
              from t t2
              where t2.id > t.id
              order by t2.id
             ) as nextid
      from t
     ) t
where nextid <> id+1;

假设 id 是表上的主键(甚至它只有一个索引)，这两种方法都应该具有合理的性能.

Assuming the id is a primary key on the table (or even that it just has an index), both methods should have reasonable performance.

这篇关于查找 SQL 中的所有整数间隙的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！