"【Question】 In the following table sequence, column A misses some values: A B C 1001& .."

blackduckie RaqForum 28 No.
349 View • 4 Years ago

Find Missing Values according to a Continuous Sequence

SQL(179)

【Question】

In the following table sequence, column A misses some values:

A B C

1001

1002

1003

1007

1008

1009

1010

1012

1011 I want to find the missing values in column A, that is, 1004\1005\1006 \1011. How can I do this? Thanks.

【Answer】

SQL will use a nested query to generate a continuous sequence of numbers according to column A’s smallest value and largest value and then find difference between the continuous sequence and column A. If column A contains a large number of values, finding difference is slow. To make the whole process faster, we can use a stored procedure to sort column A and then perform a merger to find the missing values. But that is complicated.

A fast and simple way of getting it done is using SPL. The Structured Process Language phrases the algorithm in a intuitive and easy to understand way:

	A
1	=db.query(“select A from table1 order by A”).(A)
2	=[to(A1(1),A1.m(-1)),A1].merge@d()

A1: Get ordered values from field A.

undefined

A2: Generate a continuous sequence of numbers according to the smallest value and the largest value in field A and use merge() function to find the difference between the continuous sequence and column A.

undefined