mysql sqlserver oracel分页sql语句
项目一直都用的hibernate,很少对分页查询写sql语句,这次做项目用的mybatis,mybatis的内部分页不是很好,用的是jdbc的游标移动,它是基于内存分页的,所以要自己写sql语句,就总结一下几种主流数据库的分页写法
1. mysql
mysql的分页比较简单,mysql数据库提供了分页处理的关键字 例如表的名称叫 procuct
select * from product limit 0,10
select top pageSize * from product where id not in (select top (pageNow - 1) * pageSize id from product order by id) order by id
select * from (select row_number() over by(id) rownum t.* from (select * from product) t where rownum <= pageNow * pageSize)where rownum > (pagenow - 1)* pageSize)
select * from (select rownum, t.* from (select * from product) t where rownum <= pageNow * pageSize)where rownum > (pagenow - 1)* pageSize)