数据库MySQL、Oracle、SQLServer、db2分页查询语句

  1. 云栖社区>
  2. 博客>
  3. 正文

数据库MySQL、Oracle、SQLServer、db2分页查询语句

tony~tian 2015-04-14 23:07:00 浏览1880
展开阅读全文

#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 * from (

select t.*,rownum from studenttb t where stuclass='2'

)where rownum>pagesize*(pagenumber-1) and rownum<=pagesize*pagenumber;

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方案:  
select top 10 * from table1
    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 * FROM (

Select 字段1,字段2,字段3,rownumber() over(ORDER BY 排序用的列名 ASC) AS rn from 表名

) AS a1 WHERE a1.rn BETWEEN 10 AND 20;

2:

select * from (

select rownumber() over(order by id asc 

) as rowid from table where rowid <=endIndex 

) where rowid > startIndex;

网友评论

登录后评论
0/500
评论
tony~tian
+ 关注