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

This article mainly explains "how to achieve SQL statement paging". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to achieve SQL sentence paging".

1. If there is an auto-growing id field, then:

Define two variables: Page,PageCount

Select top PageCount * From [tb_code] Where id > = (select min (id) from (select top (Page-1) * PageCount+1 id from [tb_code] order by id desc) as t) order by id desc

Principle, according to ID to calculate the minimum value of (Page-1) page, and then use the TOP keyword and can solve the problem.

2.SELECT TOP 10 id,username From [tb_code] where id not in

(SELECT TOP 20000 id FROM tb_code ORDER BY username)

Advantages: this method can be sorted according to any field in the table, and it is still very efficient when there are millions of records in a table, but the disadvantage is that it is slightly less efficient than the first when there is a large amount of data.

3.SELECT TOP 10 id,username From

(SELECT TOP page*pagecount id, username FROM tb_code ORDER BY username)

DERIVEDTBL ORDER BY username DESC

Advantages: this method can be sorted according to any field in the table.

The disadvantage is the lowest efficiency.

Using rownum pagination in SQL Server 2005 (introduction to the use of the rownum function)

For example, to look at the data between articles 10 and 20 from the table USER, SQL is implemented like this

SELECT * FROM (SELECT rownum rowcount,USER.* FROM USER) WHERE ROWCOUNT > = 10 AND ROWCOUNT

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.