What are SQL development examples and optimizations?
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
