The paging SQL statements for Oracle, SQL Server, and MySQL are as follows:
ORACLE:
Method 1:
Select * from
(
Select a. *, rownum Rn
From (select * From table_name)
Where rownum <= 40
)
Where rn> = 21;
Method 2:
Select * from
(
Select a. *, rownum Rn
From (select * From table_name)
)
Where rn between 21 and 40
The second method is not as efficient as the first method. The reason is that the second method needs to complete the sub-query, and the first method will end after the sub-query is executed to rownum = 40.
MySQL:
Select * From table_name limit 10, 20
It indicates that 20 data records are retrieved from 11th data records. The two parameters after limit indicate the start point and step, that is, the number of data records taken from that data record, for example, select * From table_name limit 0, 20
SQL Server2000:
Select top @ pagesize * From table_name where id not in (select top @ pagesize * (@ page-1) ID from table_name order by ID) order by ID
For SQL Server 2005:
Method 1:
Select...
From
(
Select row_number () over (order by id asc) as rownum ,......
From table_name
) As T
Where T. rownum> 10 and T. rownum <= 20
Method 2:
With datalist
(
Select row_number () over (order by O. id desc) as rownum ,......
From .....
Where ......
)
Select ......
From datalist
Where rownum between 10 and 20
