如何在运行计数器中找到SQL的“间隔”?

I'd like to find the first "gap" in a counter column in an SQL table. For example, if there are values 1,2,4 and 5 I'd like to find out 3.

我希望在SQL表中找到计数器列中的第一个“空白”。例如，如果有1、2、4和5的值，我想找出3。

I can of course get the values in order and go through it manually, but I'd like to know if there would be a way to do it in SQL.

我当然可以按顺序获取值，然后手动检查，但是我想知道是否有一种方法可以用SQL来完成。

In addition, it should be quite standard SQL, working with different DBMSes.

此外，它应该是标准的SQL，使用不同的dbms。

13 个解决方案

#1

In MySQL and PostgreSQL:

在MySQL和PostgreSQL:

SELECT  id + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )
ORDER BY
        id
LIMIT 1

In SQL Server:

在SQL服务器:

SELECT  TOP 1
        id + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )
ORDER BY
        id

In Oracle:

在Oracle中:

SELECT  *
FROM    (
        SELECT  id + 1 AS gap
        FROM    mytable mo
        WHERE   NOT EXISTS
                (
                SELECT  NULL
                FROM    mytable mi 
                WHERE   mi.id = mo.id + 1
                )
        ORDER BY
                id
        )
WHERE   rownum = 1

ANSI (works everywhere, least efficient):

ANSI(适用于任何地方，效率最低):

SELECT  MIN(id) + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )

Systems supporting sliding window functions:

支持滑动窗口功能的系统:

SELECT  -- TOP 1
        -- Uncomment above for SQL Server 2012+
        previd
FROM    (
        SELECT  id,
                LAG(id) OVER (ORDER BY id) previd
        FROM    mytable
        ) q
WHERE   previd <> id - 1
ORDER BY
        id
-- LIMIT 1
-- Uncomment above for PostgreSQL

#2

Your answers all work fine if you have a first value id = 1, otherwise this gap will not be detected. For instance if your table id values are 3,4,5, your queries will return 6.

如果您的第一个值id为1，那么您的答案都可以正常工作，否则将不会检测到这个间隙。例如，如果表id值为3,4,5，那么查询将返回6。

I did something like this

我做过类似的事情。

SELECT MIN(ID+1) FROM (
    SELECT 0 AS ID UNION ALL 
    SELECT  
        MIN(ID + 1)
    FROM    
        TableX) AS T1
WHERE
    ID+1 NOT IN (SELECT ID FROM TableX) 

#3

The first thing that came into my head. Not sure if it's a good idea to go this way at all, but should work. Suppose the table is t and the column is c:

我脑子里想到的第一件事。不确定这样做是否是个好主意，但应该行得通。假设表为t，列为c:

SELECT t1.c+1 AS gap FROM t as t1 LEFT OUTER JOIN t as t2 ON (t1.c+1=t2.c) WHERE t2.c IS NULL ORDER BY gap ASC LIMIT 1

选择t1。c+1与t的差值为t1左外连接t为t2 (t1.c+1=t2.c) t2。c为空阶，由间隙ASC极限1。

Edit: This one may be a tick faster (and shorter!):

编辑:这个可能会快一点(更短一点!)

SELECT min(t1.c)+1 AS gap FROM t as t1 LEFT OUTER JOIN t as t2 ON (t1.c+1=t2.c) WHERE t2.c IS NULL

选择min(t1.c)+1作为间隙，从t到t1左外连接t为t2 (t1.c+1=t2.c)。c是零

#4

This works in SQL Server - can't test it in other systems but it seems standard...

这在SQL Server中工作——不能在其他系统中测试它，但是它看起来很标准……

SELECT MIN(t1.ID)+1 FROM mytable t1 WHERE NOT EXISTS (SELECT ID FROM mytable WHERE ID = (t1.ID + 1))

You could also add a starting point to the where clause...

您还可以在where子句中添加一个起点……

SELECT MIN(t1.ID)+1 FROM mytable t1 WHERE NOT EXISTS (SELECT ID FROM mytable WHERE ID = (t1.ID + 1)) AND ID > 2000

So if you had 2000, 2001, 2002, and 2005 where 2003 and 2004 didn't exist, it would return 2003.

如果你有2000年，2001年，2002年，2005年，2003年和2004年都不存在，它会返回2003年。

#5

There isn't really an extremely standard SQL way to do this, but with some form of limiting clause you can do

实际上并没有一种非常标准的SQL方法可以做到这一点，但是可以使用某种形式的限制子句

SELECT `table`.`num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL
LIMIT 1

(MySQL, PostgreSQL)

(MySQL、PostgreSQL)

or

或

SELECT TOP 1 `num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL

(SQL Server)

(SQL Server)

or

或

SELECT `num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL
AND ROWNUM = 1

(Oracle)

(Oracle)

#6

Inner join to a view or sequence that has a all possible values.

具有所有可能值的视图或序列的内部连接。

No table? Make a table. I always keep a dummy table around just for this.

没有桌子吗?做一个表。我总是在这里放一个假桌子。

create table artificial_range( 
  id int not null primary key auto_increment, 
  name varchar( 20 ) null ) ;

-- or whatever your database requires for an auto increment column

insert into artificial_range( name ) values ( null )
-- create one row.

insert into artificial_range( name ) select name from artificial_range;
-- you now have two rows

insert into artificial_range( name ) select name from artificial_range;
-- you now have four rows

insert into artificial_range( name ) select name from artificial_range;
-- you now have eight rows

--etc.

insert into artificial_range( name ) select name from artificial_range;
-- you now have 1024 rows, with ids 1-1024

Then,

然后,

 select a.id from artificial_range a
 where not exists ( select * from your_table b
 where b.counter = a.id) ;

#7

My guess:

我的猜测:

SELECT MIN(p1.field) + 1 as gap
FROM table1 AS p1  
INNER JOIN table1 as p3 ON (p1.field = p3.field + 2)
LEFT OUTER JOIN table1 AS p2 ON (p1.field = p2.field + 1)
WHERE p2.field is null;

#8

For PostgreSQL

为PostgreSQL

An example that makes use of recursive query.

一个使用递归查询的例子。

This might be useful if you want to find a gap in a specific range (it will work even if the table is empty, whereas the other examples will not)

如果您希望在特定范围内找到空白，这可能是有用的(即使表是空的，它也能工作，而其他示例则不行)

WITH    
    RECURSIVE a(id) AS (VALUES (1) UNION ALL SELECT id + 1 FROM a WHERE id < 100), -- range 1..100  
    b AS (SELECT id FROM my_table) -- your table ID list    
SELECT a.id -- find numbers from the range that do not exist in main table
FROM a
LEFT JOIN b ON b.id = a.id
WHERE b.id IS NULL
-- LIMIT 1 -- uncomment if only the first value is needed

#9

This one accounts for everything mentioned so far. It includes 0 as a starting point, which it will default to if no values exist as well. I also added the appropriate locations for the other parts of a multi-value key. This has only been tested on SQL Server.

这一点解释了到目前为止所提到的一切。它包含0作为起始点，如果不存在任何值，它将默认为。我还为多值键的其他部分添加了适当的位置。这只在SQL Server上进行了测试。

select
    MIN(ID)
from (
    select
        0 ID
    union all
    select
        [YourIdColumn]+1
    from
        [YourTable]
    where
        --Filter the rest of your key--
    ) foo
left join
    [YourTable]
    on [YourIdColumn]=ID
    and --Filter the rest of your key--
where
    [YourIdColumn] is null

#10

select min([ColumnName]) from [TableName]
where [ColumnName]-1 not in (select [ColumnName] from [TableName])
and [ColumnName] <> (select min([ColumnName]) from [TableName])

#11

Here is standard a SQL solution that runs on all database servers with no change:

下面是一个标准的SQL解决方案，它可以在所有数据库服务器上运行，并且不需要更改:

select min(counter + 1) FIRST_GAP
    from my_table a
    where not exists (select 'x' from my_table b where b.counter = a.counter + 1)
        and a.counter <> (select max(c.counter) from my_table c);

See in action for;

看到在行动;

• PL/SQL via Oracle's livesql,
• 通过甲骨文livesql PL / SQL,
• MySQL via sqlfiddle,
• 通过sqlfiddle MySQL,
• PostgreSQL via sqlfiddle
• PostgreSQL通过sqlfiddle
• MS Sql via sqlfiddle
• MS Sql通过sqlfiddle

#12

It works for empty tables or with negatives values as well. Just tested in SQL Server 2012

它适用于空表或带负值的表。刚刚在SQL Server 2012中测试过

 select min(n) from (
select  case when lead(i,1,0) over(order by i)>i+1 then i+1 else null end n from MyTable) w

#13

If You use Firebird 3 this is most elegant and simple:

如果您使用Firebird 3，这是最优雅和简单的:

select RowID
  from (
    select `ID_Column`, Row_Number() over(order by `ID_Column`) as RowID
      from `Your_Table`
        order by `ID_Column`)
    where `ID_Column` <> RowID
    rows 1