各大数据库分页SQL写法

1. mysql
mysql分页的关键词是limit，看如下这句：
mysql> SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20
limit 10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行。
其实这样还是存在一些性能上的问题，更多看这里；

2. oracle
oracle的分页就用这一句：
SELECT *
FROM (SELECT AA.*, ROWNUM RN
FROM (SELECT * FROM USERS ORDER BY ID DESC) AA
WHERE ROWNUM <= 10)
WHERE RN > 0
其实要完全理解这句SQL，还要看另外两篇文章：文章1 文章2；

3. DB2
例子：
select * from
(
select ROW_NUMBER() OVER(ORDER BY age DESC) AS ROWNUM,
name, no, age from student
) a
where ROWNUM > 20 and ROWNUM <=30
其中的over内部可以为空，这样就不排序了。

现在在WEB 应用中使用分页技术越来越普遍了，其中利用数据库查询分页是一种效率比较高的方法，
下面列出了Oracle, DB2  及 MySQL 分页查询写法。
一：Oracle
select * from (select rownum,name from table where rownum <=endIndex )
where rownum > startIndex
二：DB2
DB2分页查询
SELECT * FROM (Select 字段1,字段2,字段3,rownumber() over(ORDER BY 排序用的列名 ASC) AS rn from 表名) AS a1 WHERE a1.rn BETWEEN 10 AND 20
以上表示提取第10到20的纪录
select * from (select rownumber() over(order by id asc ) as rowid from table where rowid <=endIndex ) AS a1
where a1.rowid > startIndex
三：MySQL
select   *   from   table   limit   start,pageNum

SELECT EVENTID  , EVENTNAME  , CREATETIME  , ELAPSEDTIME  , REPEATCOUNT  , MESSAGE  , LOCALINSTANCEID , STATUS , MEMO FROM(SELECT rownumber() OVER (ORDER BY CREATETIME ASC) AS rowid FROM MS_EVENT WHERE rowid <=20) as a1 WHERE a1.rowid >10

SELECT * FROM (SELECT EVENTID  , EVENTNAME  , CREATETIME  , ELAPSEDTIME  , REPEATCOUNT  , MESSAGE  , LOCALINSTANCEID , STATUS , MEMO,
ROWNUMBER() OVER (ORDER BY CREATETIME  ASC) AS rn FROM MS_EVENT) AS a1 WHERE a1.rn BETWEEN 10 and 20
--------------------------------------------------------------------
1. 标准的rownum分页查询使用方法：
    select *
      from (select c.*, rownum rn from content c)
     where rn >= 1
       and rn <= 5

    2. 但是如果, 加上order by addtime 排序则数据显示不正确
    select *
      from (select c.*, rownum rn from content c order by addtime)
     where rn >= 1
       and rn <= 5

    解决方法，再加一层查询，则可以解决，
    select *
      from (select rownum rn, t.*
              from (select title, addtime from content order by addtime desc) t)
     where rn >= 1
       and rn <= 5
    如果要考虑到效率的问题，上面的还可以优化成（主要两者区别）
    select *
      from (select rownum rn, t.*
              from (select title, addtime from content order by addtime desc) t
             where rownum <= 10)
     where rn >= 3
----------------------------------------------------------------------
如果查询结果需要分页,从效率上讲,下面的效率应该比较高(粉红色表示需要根据实际情况填充的地方)
三类数据库中进行分页查询的高效率方法(目前来看):
Oracle:   SELECT * FROM (      
             SELECT MY_TABLE.*,ROWNUM AS MY_ROWNUM FROM (    
            /** 括号里写实际的需要查询的SQL语句**/
           ) AS MYTABLE WHERE ROWNUM <=200/**这里是一页中的最后一条记录**/ 
                ) WHERE MY_ROWNUM>=10 /**这里是一页中的第一条记录**/
SQLServer:  SELECT * FROM (
                             SELECT TOP 页面容量 * FROM (
                                SELECT TOP 页面容量*当前页码 * FROM
                                    表 WHERE 条件 ORDER BY 字段A ASC
                                     )AS  TEMPTABLE1 ORDER BY 字段A DESC
                                      ) AS TEMPTABLE2 ORDER BY 字段A ASC

MYSQL:     SELECT 语句 LIMIT 页面的第一条记录-1,页面容量