1、Mysql分页:
mysql中limit使用:
-- 查询记录行
SELECT * FROM table LIMIT 10,5; -- 11-15 [11,12,13,14,15]
-- 如果只给定一个参数,它表示返回最大的记录行数目:
SELECT * FROM table LIMIT 6; -- 检索前 6 个记录行 ,LIMIT n 等价于 LIMIT 0,n。
-- 等价于
SELECT * FROM table LIMIT 0,6;
2、Oracle分页:ROWNUM
-- firstIndex:起始索引 pageSize:每页显示的数量
select * from(select a.*,ROWNUM rn from(sql) a where ROWNUM<=(firstIndex+pageSize)) where rn>firstIndex
-- 例:查前20条数据
select * from(select a.*,ROWNUM rn from(sql) a where ROWNUM<=20) where rn >0
-- 查21-40
select * from(select a.*,ROWNUM rn from(sql) a where ROWNUM<=20+20) where rn >20
例如从表table中第11条记录开始检索20条记录,语句如下:
SELECT *
FROM (SELECT ROWNUM R,t1.* From table where ROWNUM <= 30 ) t2
Where t2.R> 10
row_number()解析函数分页查询通式:
-- firstIndex:起始索引 pageSize:每页显示的数量 orderColumn:排序的字段名
-- sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句
select * from
(select * from(select t.*,row_number() over(order by orderColumn) as rownumber from(sql) t) p where p.rownumber>firstIndex)
where rownum<=pageSize
3、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 ) where rowid > startIndex
如果Order By 的字段有重复的值,那一定要把此字段放到 over()中
select * from (
select ROW_NUMBER() OVER(ORDER BY DOC_UUID DESC) AS ROWNUM,
DOC_UUID, DOC_DISPATCHORG,DOC_SIGNER,DOC_TITLE from DT_DOCUMENT
) a where ROWNUM >20 and ROWNUM <=30