Turning pages in sqlserver

SQL Server does not have pseudo columns similar to oralce. Therefore, this attribute cannot be used for paging.

This is a page about sqlserver recently. I don't know if it will help you:

We know that in sqlserver, the top keyword is used to retrieve the first n records. If you want to obtain the first 20 pieces of data in the query result set, the SQL statement is as follows:
Select top 20 * from x_user;
Method 1:
For general tables, IDs are based on a rule order. Generally, it is automatically added. We can only discuss this situation here. In some cases, I will continue to have fun if the project encounters it in the future. However, it is applicable to the vast majority of auto-increment IDs. Therefore, we can drill a hole:
Select top 20 * from x_user order by uid;

In this way, you can sort the data according to the uid. This is the first page. Because id is auto-increment, the id of the first record on the second page must be larger than that on the previous page. And are arranged sequentially. Therefore, the SQL statement on the second page can be expressed as follows:
Select top 20 * from x_user where uid> id order by uid at the end of the previous page;

Page 3, page 4... and so on. You only need to find the last id of the previous page.

Haha. It's quite simple. This is the next page operation. As for the previous page, the usage is the same. However, you must note that the id of the number of items on the previous page must be 2 times less than the number of items displayed on each page. Why?

Example:

1, 2, 3, 4, 5 the last id of the first page = 5
6, 7, 8, 9, 10 the last id of the second page = 10
Last id of the third page = 15

The SQL statement from the first page to the second page is: select top 20 * from x_user where uid> 5 order by uid; remember, this id is 5 !!
Suppose we want to return the second page from the third page. The last id on the third page is 15. So 15-5 = 10. This is exactly twice the number of entries displayed on each page.
In other words, from the third page to the second page, the id in this SQL statement records the last id on the first page. Therefore, we need to subtract 2 times the number of entries displayed per page.

Method 2:
Use the keyword between... and.
Select top 20 * from x_user where uid between 10 and 20 order by uid;
In this way, you can get it directly. Easy? However, these two types are only limited to id, which is the type of auto-increment rule. If you want to create a random number or date. I'm afraid it won't work. But who uses that? This is good for most projects. Haha

Okay. In this case, continue to be busy with the project. :)

 

<