SQL find minimum missing value and reuse deleted key (reprint)

Reprinted from: http://blog.csdn.net/yanghua_kobe/article/details/6262550

In data processing, we often use some "self-increment" insertion methods to process data. For example student number: b07051001,b07051002 .... Data that is similar to the increment relationship.

However, if some of these records are deleted halfway through for some reason, there will be intermittent records. At this point, we may expect to reuse these missing values in the middle. Here's how to find the smallest missing value.

First, we build a test table: Tb_test (the primary key is not set to self-growth):

[C-sharp]View PlainCopyprint?

1. CREATE TABLE Tb_test
2. (
3. ID int primary key,
4. Val char (1) null
5. )

Insert some data:

[C-sharp]View PlainCopyprint?

1. INSERT into tb_test values (1,' a ')
2. INSERT into tb_test values (2,' B ')
3. INSERT into tb_test values (3,' C ')
4. INSERT into tb_test values (4,' d ')
5. INSERT into tb_test values (5,' e ')
6. INSERT into tb_test values (6,' F ')
7. INSERT into tb_test values (7,' G ')
8. INSERT into tb_test values (8,' h ')

Delete some records and create a "fault":

Delete from tb_test where ID in (1,2,4,5,7);

The data in this table is incoherent:

At this point you can see that the minimum missing value should be: 1

We can get the result with this SQL:

Select
Case
When no 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 ' minimum missing value ';

Here's a small trick that adds 1 to the ID of all the records in the table, and then matches the IDs of all the records in the source table. As long as there is an ID missing from the source table, Id+1 will have a matching value in the source table.

For example, the sequence of IDs in the source table is: 1, 2, 3, 5, 7 (a.ID and b.ID), then the id+1 sequence in the source table is: 2, 3, 4, 6, 8 (a.id+1);

In this way, you can see a.id+1=4, and a.id+1=6 and a.id+1=8 do not have matching values in b.ID. And then the minimum value: min () So the result is 4.

However, the above sequence 3,6,8 with a subquery should also be 4, and the correct answer is 1, it is clear that the sub-query such a way to deal with is not complete.

Then why should we judge the 1 alone? This is determined by the specificity of the position of 1. Since 1 is always at the forefront of the sequence (normally). There is no number in front of it, that is to say there is no a.id+1=1 (because our default sequence is growing from 1). So no number exists to determine whether or not 1 exists. So 1 needs to be considered separately.

In the same principle, we can reuse the deleted key in this way:

Just add in front: INSERT INTO Ti_test (id,val) Select ..... (IBID.).

Of course you can use the COALESCE function to merge, there are 1 and No 1 cases:

As follows:

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)

Note: The COALESCE function is used to return the first non-null value. That is, if there is no 1 in the sequence, after being filtered by the where filter, the value returned is null, at which point the Min (a.id+1) is also null, so that the returned result is 1.

Finally, it is not recommended to reuse the return value and may get duplicate keys when running multithreaded.

SQL find minimum missing value and reuse deleted key (reprint)