sqlserver&&mysql分页sql语句总结

 sqlserver分页语句

 

select top 每页显示的数量 * from 表 where 主键id not in(select top 每页显示的熟练*当前页码 主键id from 表)

 

 mysql

 

select * from 表 limit 当前页码*每页显示的数量,每页显示的数量

 

第一页索引从0开始

 

针对 sqlserver2005  id主键的生成策略不是自增的 分页解决方案

 

select * from (select *,row_number() over(order by id) as rownum from persmember) as love, persmemberinfo as q
where rownum between 0 and 10 and love.id=q.id

 

select * from (select *,row_number() over(order by id) as rownum from persmember) as love, persmemberinfo as q
where rownum between 11 and 21 and love.id=q.id

 

select * from (select *,row_number() over(order by id) as rownum from persmember) as love, persmemberinfo as q
where rownum between 22 and 32 and love.id=q.id

 

另一种写法
WITH OrderedOrders AS
(SELECT *,ROW_NUMBER() OVER (order by [id])as RowNumber FROM persmember )SELECT * FROM OrderedOrders
WHERE RowNumber between 50 and 60;