The strongest data paging method in history

Source: Internet
Author: User
SQL Server, access, and other databases do not have auxiliary columns such as rowid, _ n _, and obs. The between operator is empty and useless,
There are no dataset operators such as limit T, which truly leads countless heroes to fold
For more information, see the SQL statements of each database. The database has the SQL syntax for taking the first N records, for example, select Top N, the key issue of data paging is that the syntax of the last n records has been pondered for a long time. Finally, I am enlightened.
The method for retrieving N records after a record set:
Hypothesis:
1. One SQL statement will generate 1000 records (select unique ID, other fields from table set where condition order by sorting)
2. 20 records are displayed on each page.
3. The page 5th is displayed.
  
The implementation is as follows:
Select * from
(
Select * from (select top 20*4 Unique id, other fields from table set where condition order by sorting) as
Union all
Select * from (select top 20*5 unique ID, other fields from table set where condition order by sorting) as B
)
A
Group by unique ID, other fields having count (unique ID) = 1 order by sorting
  
Run this SQL statement. Now the N records after the record set are retrieved.
  
Detailed description:
The key application skills of this SQL statement are the condition filter statements on Union all and grouping.
You can use this technique to create a general paging method, such as passing SQL str (SQL statement), numperpage (number of entries per page), currpage (current page ), SQL paging statements that can be fully organized in the re-Function
  
Note:
If the current page is 1, you do not need to run the SQL statement. You can directly top the SQL statement.
The SQL performance cannot be tested because there is no environment, but I believe it will not be inefficient.
The running platform can run on both access and sqlserver. Other database platforms only need to change the top keyword to run locally.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.