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
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
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.
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:
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
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.