sqlserver、mysql、oracle分页

SQL server分页:

select top pageSize * from products where productID not in (select top (pageindex-1)*pageSize productId  from products order by ProductID asc) order by ProductID asc

 

MySql分页：

select * from tableName limit 10,20

oracle 分页 (转自：http://www.blogjava.net/joessy/articles/1398.html ) ：

以前分页习惯用这样的SQL语句:

select * from

(select t.*,rownum row_num from mytable t order by t.id ) b

where b.row_num between 1 and 10

结果发现由于该语句会先生成rownum 后执行order by 子句,因而排序结果根本不对,后来在GOOGLE上搜到一篇文章 ,原来多套一层select 就能很好的解决该问题,特此记录,语句如下:

select * from

(select a.*,rownum row_num from

(select * from mytable t order by t.id desc ) a

) b where b.row_num between 1 and 10