查询M页的N行数据(起始位置start:(M-1)*N 页大小size:N 结束位置end:M*N)
SQL Server:
select top size column1,column2,...
from (select top end column1,column2,...
from table_name
order by identity asc)
order by identity desc
(注意:在子查询和父查询中排序方式要相反,对任意的一列排序都可以,父查询和子查询中的排序字段相同)
MySql:
select column1,column2,...
from table_name
limit start,end;
Oracle:
select column1,column2,...
from (select rownum as r_num,column1,column2,...
<span style="white-space:pre"> </span>from table_name
<span style="white-space:pre"> </span>where r_num<end)
where r_num>start
or
<pre name="code" class="sql">select column1,column2,...
from (select rownum as r_num,column1,column2,...
<span> </span>from table_name)
where r_num>start and r_num<end;
(注意:在Oracle中rnum伪列必须开始从0开始,所以不能再Oracle中使用
select column1,column2,...
from table_name
where rownum>start and rownum<end
来分页)