Shulou(Shulou.com)05/31 Report--

This article will explain in detail about SQL development examples and optimization, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Find out continuous data such as 1, 2, 3, 48, 50, 51, 52, 53, 67, 68

Find out the starting point and focus of consecutive numbers

1,3

48,48

51,53

67,68

Create table test.range_problem (

An int not null

Primary key (a))

Insert into test.range_problem values (1)

Insert into test.range_problem values (2)

Insert into test.range_problem values (3)

Insert into test.range_problem values (48)

Insert into test.range_problem values (50)

Insert into test.range_problem values (51)

Insert into test.range_problem values (52)

Insert into test.range_problem values (53)

Insert into test.range_problem values (66)

Insert into test.range_problem values (67)

Idea 1 find the last discontiguous number

SELECT T1.A

FROM test.range_problem T1

WHERE NOT EXISTS

(

SELECT A

FROM test.range_problem T2

WHERE T2.A-1 = T1.A

)

-- idea 2 to find the end values corresponding to all rows

SELECT ROW_NUMBER () OVER (ORDER BY TBASE.A) ID

, TBASE.A

, (SELECT Min (A)

FROM test.range_problem T1

WHERE NOT EXISTS

(

SELECT A

FROM test.range_problem T2

WHERE T2.A-1 = T1.A

)

AND T1.A > = TBASE.A-- T1.An is 3 TBASE.A 48 TBASE.An is the value of each row

) A_END

FROM test.range_problem TBASE

-- idea 3, after grouping, find out all the consecutive start and end values

SELECT MIN (A) A_START, A_END

FROM (

SELECT ROW_NUMBER () OVER (ORDER BY TBASE.A) ID

, TBASE.A

, (SELECT Min (A)

FROM test.range_problem T1

WHERE NOT EXISTS

(

SELECT A

FROM test.range_problem T2

WHERE T2.A-1 = T1.A

)

AND T1.A > = TBASE.A-- T1.An is 3 TBASE.A 48 TBASE.An is the value of each row

) A_END

FROM test.range_problem TBASE

) T

GROUP BY T.A_END

-- Optimization of the above statement

WITH POT AS (

SELECT A

FROM test.range_problem T

WHERE NOT EXISTS

(SELECT A

FROM test.range_problem TC

WHERE TC.A-1 = T.A)

)

SELECT TBASE.An A_START, (SELECT MIN (A)

FROM POT T

WHERE TBASE.A

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.