Как найти "пробел" в работе счетчика с SQL?
Я хотел бы найти первый "пробел" в столбце счетчика в таблице SQL. Например, если есть значения 1,2,4 и 5, я бы хотел узнать 3.
Я могу, конечно, получить значения в порядке и пройти его вручную, но я хотел бы знать, будет ли способ сделать это в SQL.
Кроме того, он должен быть вполне стандартным SQL, работающим с разными СУБД.
Ответы
Ответ 1
В 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
Ответ 2
Ваши ответы все работают нормально, если у вас есть первое значение id = 1, иначе этот пробел не будет обнаружен. Например, если ваши значения идентификатора таблицы составляют 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)
Ответ 3
Первое, что пришло мне в голову. Не уверен, что это хорошая идея пойти таким образом, но работать. Предположим, что таблица 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
Ответ 4
Это работает в SQL Server - не может протестировать его в других системах, но кажется стандартным...
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 год.
Ответ 5
На самом деле нет особо стандартного 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
(SQL Server)
или
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)
Ответ 6
Внутреннее соединение с представлением или последовательностью, имеющей все возможные значения.
Нет таблицы? Сделайте стол. Я всегда держу фиктивный стол только для этого.
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) ;
Ответ 7
Для 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
Ответ 8
Мое предположение:
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;
Ответ 9
На этом учитывается все, что упоминалось до сих пор. Он включает 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
Я написал быстрый способ сделать это. Не уверен, что это наиболее эффективно, но выполняет свою работу. Обратите внимание, что это не говорит вам разрыв, но говорит вам идентификатор до и после разрыва (имейте в виду, что разрыв может быть несколько значений, например, 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
Ответ 11
select min([ColumnName]) from [TableName]
where [ColumnName]-1 not in (select [ColumnName] from [TableName])
and [ColumnName] <> (select min([ColumnName]) from [TableName])
Ответ 12
Вот стандартное решение 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);
См. в действии для;
Ответ 13
Он работает для пустых таблиц или с отрицательными значениями. Просто протестирован в 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
Ответ 14
Если вы используете 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
Ответ 15
-- 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
'[email protected]_NAME+' + 1
FROM '[email protected]_NAME+' mo
WHERE NOT EXISTS
(
SELECT NULL
FROM '[email protected]_NAME+' mi
WHERE mi.'[email protected]_NAME+' = mo.'[email protected]_NAME+' + 1
)
ORDER BY
'[email protected]_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('[email protected]_NAME+') + 1 FROM '[email protected]_NAME
+' WHERE '[email protected]_NAME+' < ' + cast( @var_for_cursor as VARCHAR(MAX))
SET @SQL = @sql + '
DECLARE @HIGH INT
SELECT @HIGH = MIN('[email protected]_NAME+') - 1 FROM '[email protected]_NAME
+' WHERE '[email protected]_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
Ответ 16
Обнаружено, что большинство подходов работают очень, очень медленно в mysql. Вот мое решение для mysql < 8.0. Проверено на записях 1M с промежутком в конце ~ 1сек до конца. Не уверен, что он подходит для других разновидностей SQL.
SELECT cardNumber - 1
FROM
(SELECT @row_number := 0) as t,
(
SELECT (@row_number:[email protected]_number+1), cardNumber, [email protected]_number AS diff
FROM cards
ORDER BY cardNumber
) as x
WHERE diff >= 1
LIMIT 0,1
Я предполагаю, что последовательность начинается с "1".
Ответ 17
Следующее решение:
- предоставляет данные испытаний;
- внутренний запрос, который создает другие пробелы; и
- это работает в SQL Server 2012.
Нумерует упорядоченные строки последовательно в предложении "with", а затем дважды использует результат с внутренним объединением номера строки, но смещением на 1, чтобы сравнить строку перед строкой после, ища Идентификаторы с пробелом больше 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
Внутренний запрос производит:
gap_start gap_end
3 3
9 11
Внешний запрос выдает:
gap_start
3
Ответ 18
Если ваш счетчик начинается с 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
)
Ответ 19
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]
Ответ 20
Если числа в столбце являются положительными целыми числами (начиная с 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
