Use an SQL statement to calculate and process the ticket number of the disconnected ticket

Convert multiple records using number segments after elimination into one row for display, that is, use SQL to convert rows and columns. if it is implemented through programming, it is not difficult to achieve these two points, but it is implemented through SQL, you need some skills

1. if you want to find the break number, use an SQL statement to implement it, mainly to consider performance;

2. convert multiple records in the number segment after elimination into one row for display, that is, use SQL to convert rows and columns;

It is not difficult to implement these two points through programming, but it requires some skills to implement them through SQL.

Assume that the number of used tickets is 3, 4, 5, 7, 8, 11, 12, and the minimum value is 3 and the maximum value is 12. the following SQL statement is used to calculate the disconnection number:

Select Rownum + (3-1)

From Dual

Connect By Rownum <= 12-(3-1)

Minus

Select Column_Value Txt From Table (Cast (Zltools. f_Num2list ('3, ') As Zltools. t_Numlist ))

The result is three records, 6, 9, 10.

One technique is to use Connect by Rownum to generate a sequence-increasing record set.

The SQL statement for converting the used ticket segment to one row is as follows:

With TEST (

Select Column_Value number From Table (Cast (Zltools. f_Num2list ('3, ') As Zltools. t_Numlist ))

)

Select Substr (Max (LPAD (Length (segment), 5, '0') | segment), 7, 1000) As segment

From (

Select Sys_Connect_By_Path (segment, ',') As segment

From (

Select Rownum As row number, A. Start number | '-' | (B. interrupt number-1) As segment

From (

Select Rownum As row number, number As start number

From (

Select number From TEST

Minus

Select number + 1 From TEST)

),

(Select Rownum As row number, number As interrupt number From (

Select number + 1 As number From TEST

Minus

Select number From TEST)

) B

Where A. row number = B. row number)

Start With row number = 1

Connect By (row number-1) = Prior row number)

Query Results: 3-5, 7-8, 11-12

The following tips are used:

1. use minus to find the record set of the start and end numbers of used number segments

2. use the Sys_Connect_By_Path function and tree query function to convert multiple rows into one column.

3. use the combination of Substr, Max, LPAD, and Length functions to find the longest record.

For Oracle10G and later versions, you can use a new function Wmsys. Wm_Concat, which is much faster than the previous tree query.

With TEST (

Select Column_Value number From Table (Cast (Zltools. f_Num2list ('3, ') As Zltools. t_Numlist ))

)

Select Wmsys. Wm_Concat (segment) as segment

From (

Select Rownum As row number, A. Start number | '-' | (B. interrupt number-1) As segment

From (

Select Rownum As row number, number As start number

From (

Select number From TEST

Minus

Select number + 1 From TEST)

),

(Select Rownum As row number, number As interrupt number From (

Select number + 1 As number From TEST

Minus

Select number From TEST)

) B

Where A. row number = B. row number)