Подтвердить что ты не робот

Найти наименьшее неиспользуемое число в SQL Server

Как найти наименьшее неиспользованное число в столбце SQL Server?

Я собираюсь импортировать большое количество записанных вручную записей из Excel в таблицу SQL Server. Все они имеют числовой идентификатор (называемый номером документа), но они не были назначены последовательно по причинам, которые больше не применяются, то есть теперь, когда мой веб-сайт записывает новую запись, ему необходимо присвоить ему наименьший возможный номер документа ( больше нуля), который еще не был выполнен.

Есть ли способ сделать это с помощью простого SQL или это проблема для TSQL/code?

Спасибо!

ИЗМЕНИТЬ

Особая благодарность WW за поднятие проблемы concurrency. Учитывая, что это веб-приложение, оно является многопоточным по определению, и каждый, кто столкнулся с этой проблемой, должен учитывать блокировку кода или уровня БД для предотвращения конфликта.

LINQ

FYI - это может быть выполнено через LINQ со следующим кодом:

var nums = new [] { 1,2,3,4,6,7,9,10};

int nextNewNum = (
    from n in nums
    where !nums.Select(nu => nu).Contains(n + 1)
    orderby n
    select n + 1
).First();

nextNewNum == 5

4b9b3361

ОТВЕТЫ

Ответ 1

Найдите первую строку, в которой не существует строки с Id + 1

SELECT TOP 1 t1.Id+1 
FROM table t1
WHERE NOT EXISTS(SELECT * FROM table t2 WHERE t2.Id = t1.Id + 1)
ORDER BY t1.Id

Edit:

Чтобы обрабатывать специальный случай, когда самый низкий существующий идентификатор не равен 1, вот уродливое решение:

SELECT TOP 1 * FROM (
    SELECT t1.Id+1 AS Id
    FROM table t1
    WHERE NOT EXISTS(SELECT * FROM table t2 WHERE t2.Id = t1.Id + 1 )
    UNION 
    SELECT 1 AS Id
    WHERE NOT EXISTS (SELECT * FROM table t3 WHERE t3.Id = 1)) ot
ORDER BY 1

Ответ 2

Пока не упоминается блокировка или concurrency в любом из ответов.

Рассмотрим, что эти два пользователя добавили документ почти одновременно: -

User 1                User 2
Find Id               
                      Find Id
Id = 42               
                      Id = 42
Insert (42..)  
                      Insert (42..)
                      Error!

Вам либо необходимо: a) Обращайтесь с этой ошибкой и снова обходите цикл, ища следующий доступный Id, OR b) Сделайте блокировку в начале процесса, так что только 1 пользователь ищет идентификаторы в определенное время

Ответ 3

Если вы отсортируете их по числовому идентификатору, то номер, который вы ищете, будет первым, для которого функция ROW_NUMBER() не равна идентификатору.

Ответ 4

SELECT TOP 1 t1.id+1
FROM mytable t1
 LEFT OUTER JOIN mytable t2 ON (t1.id + 1 = t2.id)
WHERE t2.id IS NULL
ORDER BY t1.id;

Это альтернатива ответам, использующим коррелированные подзапросы, данные @Jeffrey Hantlin и @Darrel Miller.

Однако политика, которую вы описываете, на самом деле не очень хорошая идея. Значения идентификатора должны быть уникальными, но не обязательно должны быть последовательными.

Что произойдет, если вы отправите кому-то ссылку со ссылкой на документ № 42, а затем удалите документ? Позже вы повторно используете идентификатор # 42 для нового документа. Теперь получатель письма будет следовать за ссылкой на неправильный документ!

Ответ 5

Если в последовательности есть пробелы, вы можете найти первый пробел с чем-то вроде этого:

select top 1 (found.id + 1) nextid from (select id from items union select 0) found
    where not exists (select * from items blocking
                          where blocking.id = found.id + 1)
    order by nextid asc

Другими словами, найдите наименьший идентификатор, преемник которого не существует, и верните этот преемник. Если нет пробелов, он возвращает один больше, чем наибольший сохранившийся идентификатор. Идентификатор метки-заполнителя 0 устанавливается для обеспечения того, чтобы считались идентификаторы, начинающиеся с 1.

Обратите внимание, что это займет не менее n log n времени.

Microsoft SQL разрешает использование предложения from в инструкции insert, поэтому вам может не потребоваться процедурный код.

Ответ 6

declare @value int

select @value = case 
                  when @value is null or @value + 1 = idcolumn 
                    then idcolumn 
                  else @value end
   from table
   order by idcolumn

select @value + 1

Сканирует ли 1 сканирование таблицы, а не 2, хеш-совпадение и соединение, как верхний ответ

Ответ 7

Есть ли причина, что это должно быть наименьшее возможное число? Зачем вам заполнять отверстия?

Изменить, чтобы объявить ответ, поскольку это бизнес-правило.

DECLARE @counter int
DECLARE @max
SET @counter = 0
SET @max = SELECT MAX(Id) FROM YourTable
WHILE @counter <= @max
BEGIN
    SET @counter = @counter + 1
    IF NOT EXISTS (SELECT Id FROM YourTable WHERE Id = @counter)
        BREAK
    END
END

(У меня нет db, поэтому это может быть не на 100% точным, но вы можете получить его оттуда)

Ответ 8

select
    MIN(NextID) NextUsableID
from (
    select (case when c1 = c2 then 0 
            else c1 end) NextID 
    from (  select ROW_NUMBER() over (order by record_id) c1, 
                   record_id c2
            from   myTable)
)
where NextID > 0

Ответ 9

Вот простой подход. Это может быть не быстро. Он не найдет недостающие номера в начале.

SELECT MIN(MT1.MyInt+1)
FROM MyTable MT1
LEFT OUTER JOIN MyTable MT2 ON (MT1.MyInt+1)=MT2.MyInt
WHERE MT2.MyInt Is Null

Ответ 10

Вам действительно нужно попытаться преобразовать столбец в IDENTITY. BACKUP сначала используйте ROW_NUMBER для обновления идентификатора документа, чтобы они начинались с 1 и до количества документов. Вы должны сделать это в WHILE в то время, потому что, если столбец числа используется как ссылка в других таблицах (внешние ключи), SQL Server попытается обновить внешние ключи и, возможно, выйти из строя из-за конфликтов. В конце просто включите спецификации идентификации для столбца.

:) Теперь это больше работает, но позже это сэкономит вам много неприятностей.

Ответ 11

Я знаю, что этот ответ задерживается, но вы можете найти наименьшее неиспользованное число, используя выражение рекурсивной таблицы:

CREATE TABLE Test
(
    ID int NOT NULL
)

--Insert values here

;WITH CTE AS
(
    --This is called once to get the minimum and maximum values
    SELECT nMin = 1, MAX(ID) + 1 as 'nMax' 
    FROM Test
    UNION ALL
    --This is called multiple times until the condition is met
    SELECT nMin + 1, nMax 
    FROM CTE
    WHERE nMin < nMax
)

--Retrieves all the missing values in the table. Removing TOP 1 will
--list all the unused numbers up to Max + 1
SELECT TOP 1 nMin
FROM CTE
WHERE NOT EXISTS
(
    SELECT ID
    FROM Test
    WHERE nMin = ID
)

Ответ 12

Предположим, что ваши идентификаторы всегда должны начинаться с 1:

SELECT MIN(a.id) + 1 AS firstfree
FROM (SELECT id FROM table UNION SELECT 0) a
LEFT JOIN table b ON b.id = a.id + 1
WHERE b.id IS NULL

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

Единственное, что мне не нравится в этом решении, это то, что дополнительные условия должны быть включены дважды:

SELECT MIN(a.id) + 1 AS firstfree
FROM (SELECT id FROM table WHERE column = 4711 UNION SELECT 0) a
LEFT JOIN table b ON b.column = 4711 AND b.id = a.id + 1
WHERE b.id IS NULL

Также обратите внимание на комментарии о блокировке и concurrency - требование заполнения пробелов в большинстве случаев плохое проектирование и может вызвать проблемы. Однако у меня были все основания для этого: идентификаторы должны быть напечатаны и напечатаны людьми, и мы не хотим иметь идентификаторы с несколькими цифрами через некоторое время, а все низкие - бесплатны...

Ответ 13

Я столкнулся с подобной проблемой и придумал следующее:

Select Top 1 IdGapCheck
From (Select Id, ROW_NUMBER() Over (Order By Id Asc) AS IdGapCheck
    From dbo.table) F
Where Id > IdGapCheck
Order By Id Asc