Analysis of pagination thought + pagination practice

Time:2020-12-2

1、 Exclude top pagination (self named, nonstandard)

Idea: the so-called “exclude top paging” mainly relies on the two core steps of “exclude” and “top”. First, the data before the current page number is queried, and then the data is excluded from the total data. The data of the current page number can be obtained by obtaining the first n records from the remaining data.

Example – pagination condition: display 2 records per page, see page 3

Implement and explain the steps in SQL Server syntax

Step 1: write the basic query statements and sort them according to the field fields, which will be used as the subsequent data filtering criteria

1 select * from tableName order by id

 

Step 2: get a formula to calculate the total number of records on all pages before the current page number

If the current page of the example is page 3, then there are 2 pages before page 3, and each page displays 2 items. Finally, it can be concluded that the total number of previous pages is: 2 times 2 equals to 4 records. Then according to the logic, the calculation formula is obtained: (current page number – 1) * the number of display items per page, and according to the example pagination condition, we can get: (3-1) * 2

 

Step 3: add the formula in step 2 to the statement in step 1 as the value of top, so as to find out all the data before the current page.

1  select top ((3-1)*2) * from tableName  order by id

 

Step 4: exclude “all data before the current page” from the total data, and then sort it

1 select * from tableName
2 
3 Where id not in ( Select top  ((3-1)*2) * id from order by id )
4 
5 Order by id

 

Step 5: in the statement of step 4, take out the first n items (the number of items displayed on each page)

1 select  top 2 * from tableName
3 Where id not in ( select top  ((3-1)*2) * id from order by id )
5 Order by id

 

SQLiteDatabase writing method:

1 select * from tableName where id
2  not in (select did from DishInfo order by did limit (3-1)*2 )  
3 order by did limit 2 

Note: there is no top keyword in SQLite database, but the limit keyword is used to get the top n entries.

 

OracleDatabase writing method:

1 Select * from 
2 Select * from tableName where id not in (
3 Select id from (Seletc id from tableName where rownum<=(3-1)*2 order by id ) 
4 ) order by id ) where rownum<=2

Note: there is no top keyword in Oracle database, but rownum is used to get the first n entries.

 


2、 Cursor offset method (self named, nonstandard)

The idea is similar to the concept of cursor, which is mainly used in SQLite database, combined with limit and offset keywords.

Example – pagination condition: display 2 records per page, see page 4

Pagination idea:

1. Find the starting point of the offset, and then start the downward offset from the last data on the previous page.

The calculation formula of migration starting point is as follows:(Current page-1)*Number of items per page

Data example:

2. Get the number of offsets, which is equal to the number of items displayed on each page. According to the example pagination condition, the offset is set to 2.

3. Get the data of the specified page according to the offset starting point and offset number.

Data example:

Note: all the row data offset to is the current page data, excluding the row where the offset start point is.

SQL written according to the idea

1 select * from tableName order by id Limit 2 OFFSET (4-1)*2

 

Offset: Specifies the starting point of the offset, which is the last line of the previous page

Limit: indicates the number of offsets

This idea is similar to the idea of substring string interception. Offset is equivalent to defining the position of interception, and limit is equivalent to the number of interceptions.

 


 

3、 Specified range interval acquisition method (self named, nonstandard)

Thought:One specified for the queryUnique continuityAnd combined with the pagination conditions to calculate the filter range, the ID is filtered to get the data of the current page.

Example – pagination conditions: display per page2Records, see3Page data

It is implemented in SQL Server syntax as follows:

Steps:

1. Use row_ The number function generates a unique continuous identifier for each row of the query data, which can be used as a condition field for the filter range

1 Select * ,rn=row_number()  over (order by id) from tableName

2. Get the starting position of the filter data, which isThe total number of pages before the current pageFor example, if there are two pages before the third page of the current page, and two records are displayed on each page, then the total number of pages before the current page is: (3-1) * 2, because to display the first item on page 3, the calculation formula is + 1, and the final formula is: (current page-1) * number of items displayed per page + 1.

 

3. Get the end position of filtering data, which isThe number of all records for the current page and all previous pagesThis is the last item on the current page. For example, if the current page is the third page and two records are displayed per page, then the number of all records on the current page and all previous pages is: 3 * 2, and the formula is: current page * number of displayed records per page.

 

4. Nest the statements in step 1 because row is nested_ The fields generated by number must be nested before they can be filtered as criteria. Then, the start position and end position obtained in step 2 and step 3 are used as range conditions. According to the pagination condition of the example, the final statement is as follows:

1 Select * from (
2 
3 Select * ,rn=row_number() over (order by id) from tableName
4 
5 ) temp where rn between (3-1)*2+1 and 3*2

 

Implementation of Oracle Database

Example – paging condition: display 2 records per page to view the data on page 3

The idea of Oracle and SQL server is the same. In step 1, Oracle needs to sort the unique ID before generating the unique ID, and then nest the query in the outer layer to generate the unique ID of the row.

1 Select * from (
2 
3 Select t.*,rownum as rn  (select * from tableName order by id) t
4 
5 ) where rn between (3-1)*2+1 and 3*2

 


 

4、 Dynamic paging of stored procedures

SQL server stored procedure reference code:

1 -- dynamic data paging stored procedure
 2 create proc sp_DataPaging
 3 @ PageIndex int, - current page number
 4 @ PageSize int, - number of items per page
 5 @tableName varchar(200),
 6 @ orderbyfield varchar (20), -- sort field
 7 @ rowcount int output, - Total records
 8 @ pagecount int output -- total pages
 9 as
10 begin
11 
12 declare @tempSql nvarchar (max),@beginIndex int ,@endIndex int
13 
14 -- 1. Calculate the total number of records
15 set @tempSql='set @rowCount= (select count(*) from '[email protected]+')'
16  execute sp_executesql @tempSql, N'@rowCount int output',@rowCount output
17 
18 /*
Find the total number of pages = the total number of records divided by the number of displayed pages per page
The purpose of multiplying 20 * by 1.0 is to make the result a decimal, so as to round up the decimal through ceiling, so as to solve the problem that the last page does not meet the number of displayed items
21 */
22  set @pageCount=CEILING(@rowCount*1.0/@pageSize*1.0)
23 
24 -- 3
25 set @beginIndex=(@pageIndex-1)*@pageSize+1
26 set @[email protected]*@pageSize
27 
28 -- 4. Generate the statement to query the current page data
29 set @tempSql=' select * from (
30 select  *,rd=ROW_NUMBER() over (order by '[email protected]+')  from '[email protected]+' 
31 ) temp  where rd between '+cast(@beginIndex as varchar)+' and '+cast(@endIndex as varchar) +' '
32 
33 -- 5. Execute statement to query current page data
34 exec (@tempSql)
35 end
36 go
37 
38 -- Test 
39 select * from Students order by StudentId 
40 declare @pageIndex  int, @pageSize int , @rownum int   ,@pagenum int ,@tableName varchar(200),@orderByField varchar(20) 
41 begin
42 set @pageIndex=5
43 set @pageSize=5
44 set @tableName='Students'
45 set @orderByField='studentid'
46 end
47  exec sp_DataPaging @pageIndex,@pageSize,@tableName,@orderByField,@rownum output,@pagenum output
48 go

 

Oracle stored procedure reference code:

1 CREATE OR REPLACE PROCEDURE sp_DataPaging
 2  (
 3 tablename in varchar, -- table name
 4 orderbyfile in varchar, -- sort field
 5 PageIndex in number, - current page number
 6 PageSize in number, -- number of items per page
 7 rowtotal out number, - total number
 8 pagecount out number, - total pages
 9  P_CUR_OUT    OUT SYS_REFCURSOR
10 )
11 AS
12   tempSql Varchar2(800);
13   beginIndex NUMBER;
14   endIndex NUMBER;
15 BEGIN
16 
17 -- 1. Find the total number of items
18 tempSql:='select count(*) from '||tableName;
19 EXECUTE IMMEDIATE tempSql INTO rowtotal;
20 
21 -- 2. Find the total number of pages
22 pageCount:=CEIL(rowtotal/pageSize);
23 
24 -- 3. Calculate the start and end ranges of filtered data
25 beginIndex:=(pageIndex-1)*pageSize+1;
26 endIndex:=pageIndex*pageSize;
27 
28 -- 4. Generate the statement to query the current page
29 tempSql:='SELECT * FROM  (
30 SELECT t.*,ROWNUM AS rn FROM  (SELECT *  FROM '||tableName||' ORDER BY '||orderByFiled||' ) t
31 ) WHERE rn BETWEEN '||beginIndex||' AND    '||endIndex;
32 
33 -- 5. Execute query
34  --EXECUTE IMMEDIATE tempSql;
35 OPEN P_CUR_OUT FOR tempSql;
36 
37 dbms_output.put_line(tempSql);
38 END;

 

5 ASP.NET Implementation of calling stored procedure paging + dynamic navigation bar

Case rendering:

 

Source code address: https://pan.baidu.com/s/1cWyAI1rQalvfcYl4sSv1Tw

Extraction code: 9vcq

 

 

Thoughts: in the era of knowledge technology and rapid iteration, programmers should have a certain amount of code and knowledge accumulation. Everyone’s brain is not a computer, and it is impossible to remember everything forever.