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

This article introduces you how to achieve data paging query operation in SQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Method 1:

Select top 5 * from [StuDB]. [dbo]. [ScoreInfo] where [SID] not in (select top 10 [SID] from [StuDB]. [dbo]. [ScoreInfo] order by [SID]) order by [SID]

Results:

This method first takes out the SID of the first 10 items (the first two pages), excludes the SID of the first 10 pieces of data, and then takes out the first 5 pieces of data from the rest of the data.

The disadvantage is that it traverses all the data in the table twice, and the performance is not good when the amount of data is large.

Method 2:

Select top 5 * from [StuDB]. [dbo]. [ScoreInfo] where [SID] > (select MAX (t.[ Sid]) from (select top 10 [SID] from [StuDB]. [dbo]. [ScoreInfo] order by [SID]) order by [SID]

Results:

This method first takes out the SID of the first 10 pieces of data, then takes out the maximum value of SID, and then takes out the first five pieces of data from the data that are greater than the maximum value of the first 10 pieces of SID.

The disadvantage is that the performance is relatively poor, which is more or less the same as the method.

Method 3:

Select * from (select *, ROW_NUMBER () over (order by [SID]) ROW_ID from [StuDB]. [dbo]. [ScoreInfo]) t where t. [SID] between (5 * (3-1) + 1) and 5x 3

Results:

This method is characterized by the use of the ROW_NUMBER () function, which performs better than the first two methods and traverses all the data only once. Applicable to versions after Sql Server 2000 (not included).

Method 4:

Select * from [StuDB]. [dbo]. [ScoreInfo] order by [SID] offset 5 rows fetch next 5 rows only

Results:

This method applies to versions later than Sql Server 2008 (not included).

My understanding of the code offset 10 rows fetch next 5 rows only is: skip the first 10 pieces of data (the first 2 pages) and start with the next 5 pieces of data.

On how to achieve data paging query operation in SQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.