Empty SQL note

When writing this article, refer to SQL hacks, which uses SQL Server 2008

We often use auto-increment numbers when creating tables. For example, we use identity in SQL Server, sequence in Oracle, and auto_increment in MySQL.

These are all very useful, but there is often a problem that does not affect the normal operation of the software, for example:

There is now a table:

Create Table invoice
(
[ID] int identity (1, 1 ),
[Customer] varchar (10)
)

Insert four records at will.

Id customer

1

2 B

3 C

4

Then, delete the record of B and change it:

Id customer

1

3 C

4 d

Then insert a new E:

Insert into invoice (customer)
Values 'E'

The result is as follows:

Id customer

1

3 C

4 d

5 e

The problem arises: in many cases, ordinary users want to add E to the original B, instead of the following: child 1 3 4 5 missing 2!

Although this problem does not affect the normal functions of the software, it is a flaw in the user's eyes.

In SQL hacks, this problem is called "empty". The solutions are as follows:

First, we can use a SELECT statement to find all holes:

select x.id as x_id , x.customer , y.id  as y_idfrom invoice as x left join invoice as y  on ( x.id+1 = y.id  )

We should get:

X_id customer y_id

1 A null

2 B 3

3 C 4

4 d 5

5 e null

It can be found that "holes" are located at 2nd locations and 6th locations.

The figure above is the join process. It is easy to understand that the 2nd locations are "holes ".There is nothing after 5, so it is also seen as "empty"

Then we use a SELECT statement to find the first "empty!

Select coalesce (min (X. ID), 0) + 1 as the first missing number from invoice as xleft join invoice as Y on (X. ID + 1 = y. ID) where Y. ID is null

The result is:

The first empty Omission

2

Okay, now you can insert it into "empty ".

insert into invoice (id ,customer)select coalesce(min(x.id) , 0)+1   ,   'drank bar'from invoice as xleft join invoice  as y on ( x.id+1=y.id )where  y.id  is null 

Value reminder: If there is no holes in the middle, the record will be inserted here to the end, because as I said just now, he treats nothing after five as "holes ".

Note:

Coalesce (Part1, Part2) functions mean:

If Part1! = NULL return Part1

Else return Part2