如何在列中找到未使用的ID？

Question

可能是重复问题：
SQL查询以查找缺失的序列号

我有一个表格，其中有一个用户ID列，用户可以选择将哪个用户ID添加到表格中。我想知道是否有一个SQL代码可以指向未使用的用户ID列表，甚至只是最小的未使用ID？

例如，我有以下ID

USER_ID 1 2 3 5 6 7 8 10

我想知道是否有一种方法可以选择4，甚至选择4和9？

- Nap

---

请勿重复：https://dev59.com/lXNA5IYBdhLWcg3wL6yx - Eric

记录一下：创建一个带有序列的临时表，然后将序列表左连接到您的表。但是这在那个重复的答案中已经提到了。 - Eric

Answer 1

您可以尝试使用“NOT IN”子句：

select
    user_id
from table
where
    user_id not in (select user_id from another_table)

就像这样：

select
    u1.user_id + 1 as start
from users as u1
  left outer join users as u2 on u1.user_id + 1 = u2.id
where
    u2.id is null

参考这篇文章。

- Mr. Smith

---

谢谢，但这个需要另一个表。如果它是独立的，是否可能？ - Nap

我更新了我的回答，请看看是否适合您。 - Mr. Smith

请注意，这只会给出一些未使用的标识符。如果有多个连续的未使用标识符，则仅返回最低的标识符。 - Oskar Berggren

Answer 2

这取决于您使用的数据库。如果您使用的是Oracle，则可以尝试以下操作： 步骤1：查找表中userid的最大值：

    select max(userid) from tbl_userid 

    let this number be m

步骤二：在以下查询中找出rownum的最大值

    select rownum from all_objects

第三步：如果最大值大于m，则可以使用以下查询列出未使用的用户ID。

    select user_id 
    from tbl_userid
    where user_id NOT IN (select rownum from all_objects)

如果第二步返回的最大值小于m，您可以将查询调整为以下内容。

    select user_id 
    from tbl_userid
    where user_id NOT IN 
                  (select rownum 
                   from (select * 
                         from all_objects 
                         UNION ALL 
                         select * from all_objects)
                  )

重复使用UNION ALL，直到您获得max(rownum) >= m。

如果您正在使用SQL Server，请告诉我。在SQL Server中没有ROWNUM伪列的直接等效项，但可以使用RANK()函数来解决问题。

- bkm

Answer 3

这是一种使用SQL Server 2005或更高版本的方法。它可能对您有效，也可能无效：

insert into T values
  (1),(2),(3),(5),(6),(9),(11);

with Trk as (
  select userid,
    row_number() over (
      order by userid
    ) as rk
  from T
), Truns(start,finish,gp) as (
     select -1+min(userid), 1+max(userid),
     userid-rk
     from Trk
     group by userid-rk
), Tregroup as (
  select start, finish,
    row_number() over (
      order by gp 
    ) as rk
  from Truns
), Tpre as (
  select a.finish, b.start
  from Tregroup as a full outer join Tregroup as b
  on a.rk + 1 = b.rk
) 
  select
    rtrim(finish) + case when start = finish then '' else + '-' + rtrim(start) end as gap
  from Tpre
  where finish+start is not null
drop table T;

- Steve Kass

Answer 4

鉴于SQL通常是基于集合的语言，我能想到的唯一方法是创建完整的ID集，并在没有匹配ID的情况下外连接您的表。问题在于，如果您的表有大量记录，则必须生成一个临时表，其中包含从1到MAX(USER_ID)的每个ID。对于具有数千万或数亿条记录的表，这可能非常缓慢。

只是出于好奇，为什么您需要知道ID空缺？是否有某些特定原因，还是只是想不“浪费”ID？考虑到查找空洞的处理工作，我认为最好的方法是让它们存在，这样更有效率。

- jrista

---

我不想浪费员工的固定ID号码集，因为它是固定的。 - Nap

Answer 5

除了循环遍历所有ID（也许使用二叉搜索树逻辑？）我没有一个好的答案给你。

我想问一下你需要这个做什么？由于它们的本质，ID基本上是没有意义的 - 它们只是标识一些数据，而不是描述它，因此如果您的用户ID存在大的间隔，这应该不是问题。（事实上，有些人会说，拥有无法猜测的ID甚至更好，以避免用户篡改信息以找到安全漏洞）

- nickf

---

我同意让ID难以猜测是更好的选择。但是对于我需要的用户ID来说，它是一组固定的字符，例如如果限制为10000，则需要使用从00001到10000的几乎所有数字。因此，我尝试在添加时提供下一个可用的ID，但同时也给他们自由选择其他可用的ID。 - Nap