1. 总结
1.1. 概述
MySQL与Oracle的分页语法完全不同，互不兼容PostgreSQL、MariaDB是后起之秀，同时支持MySQL与Oracle12C+的分页语法SQLite只支持MySQL的分页语法
1.2. 分页SQL总结
1.2.1. MySQL
-- 1.MySQL写法（不支持Oracle）
-- 1.1.偏移量 + 页大小
select * from demo limit 20, 10; -- 不支持PostgreSQL
select * from demo limit 10 offset 20;
-- 1.2.页大小
select * from demo limit 10;
-- 2.3.偏移量
-- 不支持
1.2.2. Oracle12C+
-- 2.Oracle12C+语法（不支持MySQL、SQLite）
-- 2.1.偏移量 + 页大小
select * from demo offset 20 rows fetch next 10 rows only;
-- 2.2.页大小
select * from demo fetch next 10 rows only;
-- 2.3.偏移量
select * from demo offset 20 rows;
1.2.3. Oracle11g-
-- 3.Oracle11g-写法（不支持MySQL、SQLite）
-- 3.1.偏移量 + 页大小
-- 包2层，这样可以不影响原始sql
-- 写法一（性能低）
select * from -- 套第2层，用rownum分页
(
select rownum as rn, tmp.* from -- 套第1层，获取rownum。注意rownum必须起别名，因为rownum本质实时生成的
( select * from demo ) tmp -- 原始sql
)
where rn <= 30 and rn > 20;
-- 写法二（性能高）
select * from -- 套第2层，用rownum分页
(
select rownum as rn, tmp.* from -- 套第1层，获取rownum。注意rownum必须起别名，因为rownum本质实时生成的
( select * from demo ) tmp -- 原始sql
where rownum <= 30
)
where rn > 20;
-- 3.2.页大小
-- 包1层，这样可以不影响原始sql
select * from (select * from demo) where rownum <= 10;
-- 包0层，影响原始sql,需在原始sql的where条件中拼rownum
select * from demo where rownum <= 10;
-- 3.3.偏移量
-- 包2层，这样可以不影响原始sql
select * from -- 套第2层，用rownum分页
(
select rownum as rn, tmp.* from -- 套第1层，获取rownum。注意rownum必须起别名，因为rownum本质实时生成的
( select * from demo ) tmp -- 原始sql
)
where rn > 20;
2. 详解
逻辑：每页10条，取第3页。即取第21~30条数据
2.1. MySQL、SQLite
-- 1.MySQL写法（不支持Oracle）
-- 1.1.偏移量 + 页大小
select * from demo limit 20, 10; -- 不支持PostgreSQL
select * from demo limit 10 offset 20;
-- 1.2.页大小
select * from demo limit 10;
-- 2.3.偏移量
-- 不支持
2.2. Oracle12C+
Oracle11g之前很难用，Oracle12C+与MySQL用法格式一样了，只是语法关键字不一样，而且比较啰嗦 语法
OFFSET是偏移量，常数，不写默认为0，常用于分页。FETCH NEXT 1 ROWS 等同于 FETCH FIRST 1 ROW。only只返回指定的量，with ties 返回和最后一条数据相同的数据。
[OFFSET offset ROWS] FETCH NEXT [ row_count | percent PERCENT ] ROWS [ ONLY | WITH TIES ]
-- 2.Oracle12C+语法（不支持MySQL、SQLite）
-- 2.1.偏移量 + 页大小
select * from demo offset 20 rows fetch next 10 rows only;
-- 2.2.页大小
select * from demo fetch next 10 rows only;
-- 2.3.偏移量
select * from demo offset 20 rows;
2.3. Oracle11g-
写法一比写法二性能高。详见https://blog.csdn.net/blood_Z/article/details/123524415
-- 3.Oracle11g-写法（不支持MySQL、SQLite）
-- 3.1.偏移量 + 页大小
-- 包2层，这样可以不影响原始sql
-- 写法一（性能低）
select * from -- 套第2层，用rownum分页
(
select rownum as rn, tmp.* from -- 套第1层，获取rownum。注意rownum必须起别名，因为rownum本质实时生成的
( select * from demo ) tmp -- 原始sql
)
where rn <= 30 and rn > 20;
-- 写法二（性能高）
select * from -- 套第2层，用rownum分页
(
select rownum as rn, tmp.* from -- 套第1层，获取rownum。注意rownum必须起别名，因为rownum本质实时生成的
( select * from demo ) tmp -- 原始sql
where rownum <= 30
)
where rn > 20;
-- 3.2.页大小
-- 包1层，这样可以不影响原始sql
select * from (select * from demo) where rownum <= 10;
-- 包0层，影响原始sql,需在原始sql的where条件中拼rownum
select * from demo where rownum <= 10;
-- 3.3.偏移量
-- 包2层，这样可以不影响原始sql
select * from -- 套第2层，用rownum分页
(
select rownum as rn, tmp.* from -- 套第1层，获取rownum。注意rownum必须起别名，因为rownum本质实时生成的
( select * from demo ) tmp -- 原始sql
)
where rn > 20;
2.3.1. 错误写法
查不到任何数据，详见https://blog.csdn.net/wangmx1993328/article/details/90664207
-- 注意：如下所示是错误的，不会查到记录。因为 rownum 是从1开始，永远不会大于2，where 条件永不成立，因为没有结果查询出来，所以 rownum 也不能加一，永远是1
select * from demo where rownum > 10;
select * from demo where rownum = 10;
select * from demo where rownum >= 10;
-- 原理同上，不等于10，只能取到前9条记录
select * from demo where rownum != 10;
参考链接
发表评论