sql查找最小缺失值与重用被删除的键-阿里云开发者社区

开发者社区> 数据库> 正文

sql查找最小缺失值与重用被删除的键

简介:

在数据处理时,我们经常会使用一些“自增”的插入方式来处理数据。比如学生学号:B07051001,B07051002....类似的递增关系的数据。

但是,如果中途因为某些原因将其中的一些记录删除掉之后,就会出现断续的记录。这时,我们可能期待将这些中间的缺失值再次利用。以下,就谈谈如何查找最小缺失值。

首先,我们建一个测试表:tb_Test(主键并未设置为自增长):

create table tb_Test ( id int primary key, val char(1) null )

插入一些数据:

insert into tb_Test values(1,'a') insert into tb_Test values(2,'b') insert into tb_Test values(3,'c') insert into tb_Test values(4,'d') insert into tb_Test values(5,'e') insert into tb_Test values(6,'f') insert into tb_Test values(7,'g') insert into tb_Test values(8,'h')

删除某些记录,制造“断层”:

delete from tb_Test where id in (1,2,4,5,7);

此时表中数据为不连贯的:

此时能看出最小缺失值应该为:1

我们通过下面这段sql能够得到结果:

select
case
when not exists(select 1 from tb_Test where id=1)

then 1
else (
select min(a.id+1)
from tb_Test as a
where not exists
(
select 1
from tb_Test as b
where b.id=a.id+1
)
)
end as '最小缺失值';

这里使用了一个小的技巧,原理是将表中所有记录的id加1,再与源表中所有记录的id匹配。这样只要有源表中有id缺失,id+1在源表中就会有匹配不到的值。

比如源表中id序列为:1、2、3、5、7(a.id与b.id),则源表中的id+1序列为: 2、3、4、6、8(a.id+1);

这样再代入子查询中,就可以看到a.id+1=4,和a.id+1=6和a.id+1=8在b.id中不存在匹配值。然后再去最小值:min()这样结果就为4。

但是以上上图中的这个序列3,6,8用子查询得出的结果也应该为4,而正确答案为1,显然只是用子查询这样的方式处理是不完整的。

那为什么要把1单独判断呢?这是由1的位置的特殊性决定的。因为1开始时总是处在序列的最前端的位置(正常情况下)。它的前面已经没有数字了,也就是说不存在a.id+1=1(因为我们默认序列是从1开始增长的)。因此没有哪个数字存在与否能判断出1是否存在。所以1需要单独考虑。

处于同样的原理,我们可以用这种方式重用被删除的键:

只要在前面加上:insert into ti_Test(id,val) Select .....(同上)即可。

当然你可以使用coalesce函数来合并,存在1和不存在1的情况:

如下:

select Coalesce(Min(a.id+1),1)

from tb_Test a

where not exists (
select 1
from tb_Test as b
where b.id=a.id+1
) And exists(select 1 from tb_Test where id=1)

注:coalesce函数用于返回第一个非空值。也就是说如果序列中没有1,在被where筛选器筛选后,返回的值为null,此时min(a.id+1)也为null,这样返回的结果就为1。

最后,并不推荐重用返回值并且在多线程运行时也可能得到重复的键。




原文发布时间为:2011-03-20


本文作者:vinoYang


本文来自云栖社区合作伙伴CSDN博客,了解相关信息可以关注CSDN博客。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章