可能是重复问题:
SQL查询以查找缺失的序列号
我有一个表格,其中有一个用户ID列,用户可以选择将哪个用户ID添加到表格中。我想知道是否有一个SQL代码可以指向未使用的用户ID列表,甚至只是最小的未使用ID?
例如,我有以下ID
USER_ID 1 2 3 5 6 7 8 10
我想知道是否有一种方法可以选择4,甚至选择4和9?
可能是重复问题:
SQL查询以查找缺失的序列号
我有一个表格,其中有一个用户ID列,用户可以选择将哪个用户ID添加到表格中。我想知道是否有一个SQL代码可以指向未使用的用户ID列表,甚至只是最小的未使用ID?
例如,我有以下ID
USER_ID 1 2 3 5 6 7 8 10
我想知道是否有一种方法可以选择4,甚至选择4和9?
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
参考这篇文章。
这取决于您使用的数据库。如果您使用的是Oracle,则可以尝试以下操作: 步骤1:查找表中userid的最大值:
select max(userid) from tbl_userid
let this number be m
select rownum from all_objects
select user_id
from tbl_userid
where user_id NOT IN (select rownum from all_objects)
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()函数来解决问题。
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;
鉴于SQL通常是基于集合的语言,我能想到的唯一方法是创建完整的ID集,并在没有匹配ID的情况下外连接您的表。问题在于,如果您的表有大量记录,则必须生成一个临时表,其中包含从1到MAX(USER_ID)的每个ID。对于具有数千万或数亿条记录的表,这可能非常缓慢。
只是出于好奇,为什么您需要知道ID空缺?是否有某些特定原因,还是只是想不“浪费”ID?考虑到查找空洞的处理工作,我认为最好的方法是让它们存在,这样更有效率。
除了循环遍历所有ID(也许使用二叉搜索树逻辑?)我没有一个好的答案给你。
我想问一下你需要这个做什么?由于它们的本质,ID基本上是没有意义的 - 它们只是标识一些数据,而不是描述它,因此如果您的用户ID存在大的间隔,这应该不是问题。(事实上,有些人会说,拥有无法猜测的ID甚至更好,以避免用户篡改信息以找到安全漏洞)