写这篇文章的时候参考的是《sql hacks》,使用的是sql server 2008
我们创建表的时候,经常会使用自增的编号,比如在sql server 中用 identity ,在oracle中使用sequence,在mysql中有auto_increment
这些都很好用,只是经常会出现一个不影响软件正常运行的问题,打个比方:
现在有一个表:
create table invoice
(
[id] int identity(1,1) ,
[customer] varchar(10)
)
然后随便插入四个记录
id customer
1 a
2 b
3 c
4 4
然后删除 b 的记录,变成:
id customer
1 a
3 c
4 d
然后插入一个新的e :
insert into invoice(customer)
values 'e'
结果会得到:
id customer
1 a
3 c
4 d
5 e
问题就出现了:很多时候普通的用户希望新增的e是补在原来的b上面的,而不是像 现在这个样:子1 3 4 5中间少了个2的!
这个问题虽然不影响软件的正常功能,不过在用户的眼里就是有那么一点瑕疵。
《sql hacks》中把这个问题叫做“空洞” , 解决办法有以下:
首先我们可以用一个select语句找出所有的空洞:
select x.id as x_id , x.customer , y.id as y_id from invoice as x left join invoice as y on ( x.id+1 = y.id )
应该得到:
x_id customer y_id
1 a null
2 b 3
3 c 4
4 d 5
5 e null
可以发现“空洞”在第2个位置和第6个位置。
上面的图就是join连接的过程了,很容易明白第2个位置是个“空洞”。而5之后没有东西了,所以也看作“空洞”
接着我们用一个select语句找出第一个“空洞”吧!
select coalesce(min(x.id) , 0)+1 as 第一个遗漏的编号 from invoice as x left join invoice as y on (x.id+1=y.id) where y.id is null
结果是:
第一个遗漏的空洞
2
好吧,现在往“空洞”里面插既可以了
insert into invoice (id ,customer) select coalesce(min(x.id) , 0)+1 , 'drank bar' from invoice as x left join invoice as y on ( x.id+1=y.id ) where y.id is null
值的提醒的是:如果中间没有空洞,这里也会把记录插到最后,因为刚才说了,他把5后面没东西也看成“空洞”来对待了
注释:
coalesce(part1 , part2)这个函数的意思是:
if part1 !=null return part1
else return part2