Oracle, DB2 及 MySQL 分页查询写法

 
现在在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 (s