Как мне найти «пробел» в работе счетчика с SQL?

Я хотел бы найти первый «пробел» в столбце счетчика в таблице SQL. Например, если есть значения 1,2,4 и 5, я бы хотел узнать 3.

Конечно, я могу привести значения в порядок и просмотреть их вручную, но я хотел бы знать, есть ли способ сделать это в SQL.

Кроме того, это должен быть вполне стандартный SQL, работающий с разными СУБД.

 ajeh23 янв. 2017 г., 21:12
В SQL Server 2008 и выше вы можете использоватьLAG(id, 1, null) функция сOVER (ORDER BY id) пункт.

Ответы на вопрос(17)

Нашел большинство подходов очень,очень медленно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`.

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

которая имеет все возможные значения.

Нет стола? Сделать стол. Я всегда держу фиктивный стол только для этого.

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(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.

что пришло мне в голову. Не уверен, стоит ли вообще идти по этому пути, но это должно сработать. Предположим, что таблица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

 Michael Krelin - hacker21 авг. 2009 г., 16:08
Нет-нет, Имон,LEFT OUTER JOING t2 потребует от вас иметьt2 таблица, которая является просто псевдонимом.
 Eamon Nerbonne21 авг. 2009 г., 16:03
ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ t ==> ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ t2

если у вас первое значение 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) 
            -- 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

которое работает на всех серверах баз данных без изменений:

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 черезOracle Oracle,MySQL черезsqlfiddle,PostgreSQL черезsqlfiddleMS Sql черезsqlfiddle

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 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

а также. Только что протестирован в 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

Мое предположение:

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;
select min([ColumnName]) from [TableName]
where [ColumnName]-1 not in (select [ColumnName] from [TableName])
and [ColumnName] <> (select min([ColumnName]) from [TableName])

предоставляет данные испытаний;внутренний запрос, который создает другие пробелы; а такжеэто работает в SQL Server 2012.

Нумерует упорядоченные строки последовательно вс участием", а затем повторно использует результат дважды с внутренним соединением по номеру строки, но смещенным на 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

и вы хотите сгенерировать первое число последовательности (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
     )  
Решение Вопроса

В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
 Quassnoi20 февр. 2014 г., 16:39
@Malkocoglu: вы получитеNULLне0, если таблица пуста. Это верно для всех баз данных.
 Quassnoi16 дек. 2011 г., 18:28
@vulkanino: пожалуйста, попросите их сохранить отступ. Также обратите внимание, что лицензия Creative Commons требует, чтобы вы татуировали мой ник и вопросURL также, хотя это может быть QR-код, я думаю.
 ttomsen11 февр. 2015 г., 19:48
это не найдет начальные пробелы должным образом. если у вас есть 3,4,5,6,8. этот код сообщит 7, потому что у него НЕТ 1, чтобы даже проверить. Так что, если вам не хватает стартовых номеров, вам придется проверить это.
 vulkanino16 дек. 2011 г., 14:53
супер полезно, я буду татуировать этот код :)
 Hamish Grubijan20 мая 2013 г., 23:12
Это здорово, но если бы я[1, 2, 11, 12], то это найдет только3, Вместо этого я бы хотел найти 3-10 - в основном начало и конец каждого разрыва. Я понимаю, что мне, возможно, придется написать свой собственный скрипт на python, который использует SQL (в моем случае MySql), но было бы неплохо, если бы SQL мог приблизить меня к тому, что я хочу (у меня есть таблица с 2 миллионами строк, в которой есть пробелы, поэтому мне нужно будет нарезать его на более мелкие части и запустить на нем несколько SQL). Я полагаю, что мог бы выполнить один запрос, чтобы найти начало пропуска, затем другой, чтобы найти конец пропуска, и они "сортируют слияние" двух последовательностей.

Там на самом деле неочень стандартный способ 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)

 John Haugeland03 янв. 2017 г., 06:09
если есть интервал пропуска, для вашего запроса postgres будет возвращена только первая строка в диапазоне.

Ваш ответ на вопрос