我如何找到一个“差距”在运行 SQL 计数器？

小开

我脑子里想到的第一件事。不知道这样做是不是个好主意，但应该可以。假设表是 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

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

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

小开

最佳答案

在 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

在 SQL Server:

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

在 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(到处工作，效率最低) :

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

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

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

小开

这在 SQLServer 中可以工作-不能在其他系统中测试它，但它似乎是标准的..。

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

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

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

所以如果你有2000,2001,2002和2005，而2003和2004不存在，那么它会返回2003。

小开

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

没有桌子? 做一张桌子，我总是留一张假的桌子，就是为了这个。

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

然后,

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

小开

我猜:

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;

小开

实际上没有一种非常标准的 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)

或者

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

(SQLServer)

或者

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

(神谕)

小开

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

我做过类似的事

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)

小开

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

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

小开

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

小开

对于 PostgreSQL

使用递归查询的示例。

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

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

小开

下面是一个标准的 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);

见于行动;

通过神使的生活实现 PL/SQL,
MySQL via Sqlfiddle,
PostgreSQL via Sqlfiddle
通过 Sqlfiddle的 MS Sql

小开

它适用于空表或负值。刚刚在 SQLServer2012中进行了测试

 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

小开

如果你使用 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

小开

            -- PUT THE TABLE NAME AND COLUMN NAME BELOW
-- IN MY EXAMPLE, THE TABLE NAME IS = SHOW_GAPS AND COLUMN NAME IS = ID


-- PUT THESE TWO VALUES AND EXECUTE THE QUERY


DECLARE @TABLE_NAME VARCHAR(100) = 'SHOW_GAPS'
DECLARE @COLUMN_NAME VARCHAR(100) = 'ID'




DECLARE @SQL VARCHAR(MAX)
SET @SQL =
'SELECT  TOP 1
'+@COLUMN_NAME+' + 1
FROM    '+@TABLE_NAME+' mo
WHERE   NOT EXISTS
(
SELECT  NULL
FROM    '+@TABLE_NAME+' mi
WHERE   mi.'+@COLUMN_NAME+' = mo.'+@COLUMN_NAME+' + 1
)
ORDER BY
'+@COLUMN_NAME


-- SELECT @SQL


DECLARE @MISSING_ID TABLE (ID INT)


INSERT INTO @MISSING_ID
EXEC (@SQL)


--select * from @MISSING_ID


declare @var_for_cursor int
DECLARE @LOW INT
DECLARE @HIGH INT
DECLARE @FINAL_RANGE TABLE (LOWER_MISSING_RANGE INT, HIGHER_MISSING_RANGE INT)
DECLARE IdentityGapCursor CURSOR FOR
select * from @MISSING_ID
ORDER BY 1;


open IdentityGapCursor


fetch next from IdentityGapCursor
into @var_for_cursor


WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = '
DECLARE @LOW INT
SELECT @LOW = MAX('+@COLUMN_NAME+') + 1 FROM '+@TABLE_NAME
+' WHERE '+@COLUMN_NAME+' < ' + cast( @var_for_cursor as VARCHAR(MAX))


SET @SQL = @sql + '
DECLARE @HIGH INT
SELECT @HIGH = MIN('+@COLUMN_NAME+') - 1 FROM '+@TABLE_NAME
+' WHERE '+@COLUMN_NAME+' > ' + cast( @var_for_cursor as VARCHAR(MAX))


SET @SQL = @sql + 'SELECT @LOW,@HIGH'


INSERT INTO @FINAL_RANGE
EXEC( @SQL)
fetch next from IdentityGapCursor
into @var_for_cursor
END


CLOSE IdentityGapCursor;
DEALLOCATE IdentityGapCursor;


SELECT ROW_NUMBER() OVER(ORDER BY LOWER_MISSING_RANGE) AS 'Gap Number',* FROM @FINAL_RANGE

小开

发现大多数方法运行非常，非常慢在 mysql。这是我的 mysql < 8.0解决方案。测试在1M 记录与一个差距接近结束 ~ 1秒完成。不确定它是否适合其他 SQL 风格。

SELECT cardNumber - 1
FROM
(SELECT @row_number := 0) as t,
(
SELECT (@row_number:=@row_number+1), cardNumber, cardNumber-@row_number AS diff
FROM cards
ORDER BY cardNumber
) as x
WHERE diff >= 1
LIMIT 0,1

我假设这个序列是从“1”开始的。

小开

解决办法如下:

提供测试数据;
产生其他空白的内部查询; 以及
它在 SQLServer2012中工作。

在“ 和”子句中按顺序对排序的行进行编号，然后在行号上使用内部连接重用结果两次，但是偏移1，以便比较之前的行和之后的行，查找间隔大于1的 ID。比要求更多，但更广泛适用。

create table #ID ( id integer );


insert into #ID values (1),(2),    (4),(5),(6),(7),(8),    (12),(13),(14),(15);


with Source as (
select
row_number()over ( order by A.id ) as seq
,A.id                               as id
from #ID as A WITH(NOLOCK)
)
Select top 1 gap_start from (
Select
(J.id+1) as gap_start
,(K.id-1) as gap_end
from       Source as J
inner join Source as K
on (J.seq+1) = K.seq
where (J.id - (K.id-1)) <> 0
) as G

内部查询生成:

gap_start   gap_end


3           3


9           11

外部查询显示:

gap_start


3

小开

如果您的计数器从1开始，并且您希望在空时生成序列(1)的第一个数字，下面是对 Oracle 有效的第一个答案的更正代码:

SELECT
NVL(MIN(id + 1),1) AS gap
FROM
mytable mo
WHERE 1=1
AND NOT EXISTS
(
SELECT  NULL
FROM    mytable mi
WHERE   mi.id = mo.id + 1
)
AND EXISTS
(
SELECT  NULL
FROM    mytable mi
WHERE   mi.id = 1
)

小开

我写了一个做这件事的快速方法。不知道这是不是最有效率的，但可以完成任务。请注意，它不会告诉您间隙，而是告诉您间隙之前和之后的 id (请记住，间隙可以是多个值，例如1、2、4、7、11等)

我用 sqlite 作为例子

如果这是您的表结构

create table sequential(id int not null, name varchar(10) null);

这些是你的行

id|name
1|one
2|two
4|four
5|five
9|nine

问题是

select a.* from sequential a left join sequential b on a.id = b.id + 1 where b.id is null and a.id <> (select min(id) from sequential)
union
select a.* from sequential a left join sequential b on a.id = b.id - 1 where b.id is null and a.id <> (select max(id) from sequential);

Https://gist.github.com/wkimeria/7787ffe84d1c54216f1b320996b17b7e

小开

DECLARE @Table AS TABLE(
[Value] int
)


INSERT INTO @Table ([Value])
VALUES
(1),(2),(4),(5),(6),(10),(20),(21),(22),(50),(51),(52),(53),(54),(55)
--Gaps
--Start    End     Size
--3        3       1
--7        9       3
--11       19      9
--23       49      27




SELECT [startTable].[Value]+1 [Start]
,[EndTable].[Value]-1 [End]
,([EndTable].[Value]-1) - ([startTable].[Value]) Size
FROM
(
SELECT [Value]
,ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY [Value]) Record
FROM @Table
)AS startTable
JOIN
(
SELECT [Value]
,ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY [Value]) Record
FROM @Table
)AS EndTable
ON [EndTable].Record = [startTable].Record+1
WHERE [startTable].[Value]+1 <>[EndTable].[Value]

小开

如果列中的数字是正整数(从1开始) ，那么这里有一个简单的解法。(假设 ID 是您的列名)

    SELECT TEMP.ID
FROM (SELECT ROW_NUMBER() OVER () AS NUM FROM 'TABLE-NAME') AS TEMP
WHERE ID NOT IN (SELECT ID FROM 'TABLE-NAME')
ORDER BY 1 ASC LIMIT 1

小开

这里有一个替代方案，以便携和更紧凑的方式显示所有可能的间隙值的范围:

假设您的表模式如下所示:

> SELECT id FROM your_table;
+-----+
| id  |
+-----+
|  90 |
| 103 |
| 104 |
| 118 |
| 119 |
| 120 |
| 121 |
| 161 |
| 162 |
| 163 |
| 185 |
+-----+

要获取所有可能的间隙值的范围，您有以下查询:

子查询列出了 id 对，每个 id 对的 lowerbound列小于 upperbound列，然后使用 GROUP BY和 MIN(m2.id)来减少无用记录的数量。
外部查询进一步删除 lowerbound正好是 upperbound - 1的记录
我的查询没有(显式地)输出两端的2条记录 (YOUR_MIN_ID_VALUE, 89)和 (186, YOUR_MAX_ID_VALUE)，这意味着到目前为止在 your_table中还没有使用这两个范围中的任何数字。

> SELECT  m3.lowerbound + 1, m3.upperbound - 1 FROM
(
SELECT m1.id as lowerbound, MIN(m2.id) as upperbound FROM
your_table m1 INNER JOIN your_table
AS m2 ON m1.id < m2.id GROUP BY m1.id
)
m3 WHERE m3.lowerbound < m3.upperbound - 1;


+-------------------+-------------------+
| m3.lowerbound + 1 | m3.upperbound - 1 |
+-------------------+-------------------+
|                91 |               102 |
|               105 |               117 |
|               122 |               160 |
|               164 |               184 |
+-------------------+-------------------+