天天看點

資料庫MySQL、Oracle、SQLServer、db2分頁查詢語句

#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;