#pagesize 為每頁的大小.
#pagenumber為要顯示頁面頁面号.
#檢視學生表的2班學生
mysql查詢分頁語句:
select * from studenttb where stuclass='2' limit pagesize*(pagenumber-1),pagesize*pagenumber;
oracle查詢分頁語句:
需要分組分頁:
select * from (
select t.*,rownum from (select * from studenttb where stuclass='2' order by stugender) t
)where rownum>pagesize*(pagenumber-1) and rownum<=pagesize*pagenumber;
基本分頁:
select t.*,rownum from studenttb t where stuclass='2'
sqlserver查詢分頁語句:
查詢語句1:
select * from (
select row_number() over(order by stuno asc)as row_number,* from studenttb where stuclass='2'
)as tl where tl.row_number between pagesize*(pagenumber-1) and pagesize*pagenumber;
下邊sqlserver三種分頁方案預設頁大小為10,開始位置:頁大小*(頁數-1)
top方案:
select top 10 * from table1
where id not in(select top 開始的位置 id from table1)
max方案:
where id>(select max(id)
from (select top 開始位置 id from table1order by id)tt)
row方案:
select *
from (
select row_number()over(order by tempcolumn)temprownumber,*
from (select top 開始位置+10 tempcolumn=0,* from table1)t
)tt
where temprownumber>開始位置
效率: top方案 < max方案 < row方案(max)
db2查詢分頁語句:
1:
select 字段1,字段2,字段3,rownumber() over(order by 排序用的列名 asc) as rn from 表名
) as a1 where a1.rn between 10 and 20;
2:
select rownumber() over(order by id asc
) as rowid from table where rowid <=endindex
) where rowid > startindex;