分页sql语句

不同数据库的分页语句有一定的差异，今天小编特此来总结一下。

1.Mysql

大部分代码是这样的

SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10; 

但是推荐这样写（效率问题）

 SELECT * FROM myweb.sys_role  limit 80001,10 或SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010; 

原因：效率问题

2.SqlServer

实例
SELECT * FROM ARTICLE w1
WHERE ID in
(
SELECT top 30 ID FROM
(
SELECT top 1030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
) w ORDER BY w.YEAR ASC, w.ID ASC
)
ORDER BY w1.YEAR DESC, w1.ID DESC

3.Oracle

SELECT *  FROM (SELECT ROWNUM AS rowno, t.*          FROM emp t         WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')                             AND TO_DATE ('20060731', 'yyyymmdd')           AND ROWNUM <= 20) table_alias WHERE table_alias.rowno >= 10;