主流常见关系数据库分页sql语句写法limit、offset、fetch、rownum。MySQL、PostgreSQL、SQLite、Oracle、DB2、SQL Server。_fetch first rows only和limit

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
3
4
5
6
7
8

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
4
5
6
7

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;
                 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

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.偏移量
-- 不支持
1
2
3
4
5
6
7
8

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 ]
1

-- 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
4
5
6
7

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;              
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

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;
1
2
3
4
5
6