Scenario one (assuming the user only browses to the previous dozens of pages):
Idea: Take out the page size * Number of pages of data, and then get to Intstartindex and Intendindex direct data; Advantages: Simple writing, universal, suitable for users to only browse the first few pages of the situation disadvantage: if the amount of data is more than tens of millions, read the last few pages will be very slow. SQL Server: select top Page Size * pages * FROM table1; Get the data, and then take the corresponding data by calculation. "Oracle": SELECT * FROM (select tmp_tab.*, rownum as rn from (SELECT * from table1) tmp_tab) WHERE rn<= Page Size * pages "DB2": SELECT * FROM table1 fetch first Page size * page rows only "MySQL": select * FROM table1 limit Page size * pages Note: After limit if it is a parameter then starting from 0 scene two (user random access to specific pages): Thinking: Take out the corresponding start and end of the page bar number of advantages: can be directly taken, Cons: Writing is complex, and SQL Sqrver must specify a primary key or sort to complete the paging. "SQL Server 2000 Version": Requires primary key id select top page size from table1 where ID not in (select top page size * pages id from table1 Ord ER by-ID) Order BY id "SQL Server 2005 or higher": Requires a sort field select * FROM (select Row_number () over (order by ID) as rn,* From table1) where rn>= page starts and RN =< page size * pages) "DB2": Requires a sort field, less than sql2005 function with a lower bar number select * FROM (select RowNumber () over (or Der by id) as rn,* from table1) where rn>= page starts and RN =< pageSize * pages) "Oracle" select * FROM (select tmp_tab.*, rownum as rn from (SELECT * from table1) tmp_tab WHERE&NB Sp rn<= Page Size * pages) WHERE rn>= page start "MySQL": select * from table1 limit page start, page size
SQL Paging sql server,oracle,db2,mysql