如何在使用 SQL 运行计数器时找到“差距”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1312101/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I find a "gap" in running counter with SQL?
提问by Touko
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。
回答by Quassnoi
In MySQLand 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 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
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
回答by Ruben
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)
回答by Michael Krelin - hacker
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 tand 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
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
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
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
回答by chaos
There isn't really an extremelystandard 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 服务器)
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)
(甲骨文)
回答by Mayo
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。
回答by wwmbes
The following solution:
以下解决方案：
- provides test data;
- an inner query that produces other gaps; and
- it works in SQL Server 2012.
- 提供测试数据；
- 产生其他差距的内部查询；和
- 它适用于 SQL Server 2012。
Numbers the ordered rows sequentially in the "with" clause and then reuses the result twice with an inner join on the row number, but offset by 1 so as to compare the row before with the row after, looking for IDs with a gap greater than 1. More than asked for but more widely applicable.
在 " with" 子句中按顺序对有序行进行编号，然后使用行号的内部联接重用结果两次，但偏移 1 以便比较之前的行和之后的行，查找间隔大于的 ID 1. 要求更高，但适用范围更广。
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
The inner query produces:
内部查询产生：
gap_start gap_end
3 3
9 11
The outer query produces:
外部查询产生：
gap_start
3
回答by AlexM
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
回答by tpdi
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) ;
回答by Leonel Martins
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;
回答by Carter Medlin
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 作为起点，如果不存在任何值，它将默认为 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
