Oracle, SQL Server, Access database high effect paging tips

1. SQL Server, Access database

This is all Microsoft's database, is a family, the basic operation is similar, often using the following paging statement:

PAGESIZE: Number of records displayed per page

CurrentPage: Current page number

The name of the datasheet is: components

Index PRIMARY key word is: ID

以下是引用片段：
select top PAGESIZE * from components where id not in
(select top (PAGESIZE*(CURRENTPAGE-1))
id from components order by id)order by id

such as the following:

以下是引用片段：
select top 10 * from components where id not in
(select top 10*10 id from components order by id)
order by id

Select from 101 Records, select only the previous 10 records

2. Oracle Database

Since the Oracle database does not have the top keyword, there are two ways to do this, as Microsoft's data does:

(1), one is to use the contrary.

PAGESIZE: Number of records displayed per page

CurrentPage: Current page number

The name of the datasheet is: components

Index PRIMARY key word is: ID

以下是引用片段：
select * from components where id not
in(select id from components where
rownum<=(PAGESIZE*(CURRENTPAGE-1)))
and rownum<=PAGESIZE order by id;

The following example:

以下是引用片段：
select * from components where id not in
(select id from components where rownum<=100)
and rownum<=10 order by id;

Select from 101 to record and select the first 10.

(2), the use of minus, that is, the Chinese meaning is minus.

以下是引用片段：
select * from components where rownum
<=(PAGESIZE*(CURRENTPAGE-1)) minus
select * from components where rownum
<=(PAGESIZE*(CURRENTPAGE-2));

As an example: SELECT * from Components where

以下是引用片段：
rownum<=10 minus select * from components
where rownum<=5;.

(3), one is the use of Oracle's rownum, this is an Oracle query automatically return the sequence number, generally not shown, but can be seen through the select RowNum from [table name], note that it is from 1 to the current total number of records.

以下是引用片段：
select * from (select rownum tid,components.
* from components where rownum<=100) where tid<=10;