分页查询

本文将向您介绍如何在 OceanBase 数据库 Oracle 模式下进行分页查询操作。

前提条件

• 您已连接 OceanBase 数据库 Oracle 模式租户。更多有关连接数据库的操作信息，请参见 连接方式概述。
• 您已拥有 SELECT 权限。更多查看当前用户权限的相关操作信息，请参见 查看用户权限。如果不具备该权限，请联系管理员为您授权，有关用户授权的相关操作信息，请参见 直接授予权限。

概述

在 OceanBase 数据库 Oracle 模式下，您可以使用 Row_Limiting_Clause 来实现分页查询。Row_Limiting_Clause 用于限制 SELECT 查询返回的行数，常用于分页操作。

Row_Limiting_Clause 的语法结构如下：

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

参数说明：

创建测试表并添加测试数据

1. 创建表 employee_info。

   CREATE TABLE employee_info(
       employee_id NUMBER(10,0),
       employee_name VARCHAR2(50),
       department_id NUMBER(10,0),
       salary NUMBER(10,2),
       hire_date DATE,
       CONSTRAINT pk_employee PRIMARY KEY(employee_id)
   );
   

2. 向表 employee_info 中插入测试数据。

   INSERT INTO employee_info VALUES
   (1001,'Smith',10,8000.00,DATE'2020-01-15'),
   (1002,'Allen',20,9500.00,DATE'2019-03-20'),
   (1003,'Jones',10,12000.00,DATE'2018-06-10'),
   (1004,'Blake',30,7500.00,DATE'2021-02-28'),
   (1005,'Scott',20,11000.00,DATE'2019-11-05'),
   (1006,'Ford',10,9000.00,DATE'2020-08-12'),
   (1007,'King',30,8500.00,DATE'2020-05-18'),
   (1008,'Turner',20,10000.00,DATE'2019-09-25'),
   (1009,'Adams',10,10500.00,DATE'2020-12-01'),
   (1010,'Miller',30,9200.00,DATE'2021-01-10');
   

使用 ORDER BY 分页

分页查询必须使用 ORDER BY 子句。如果不使用 ORDER BY，数据库不保证查询结果的顺序，可能导致：

• 不同时间执行相同查询返回不同的结果顺序
• 分页查询出现数据重复或遗漏
• 分页结果不稳定，影响用户体验

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
ORDER BY employee_id 
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

使用唯一字段排序

您必须使用唯一字段（如主键）进行排序，以确保分页结果的唯一性和稳定性。

如果 ORDER BY 使用的字段不是唯一的，对于具有相同排序值的行，它们的顺序是不确定的，可能导致分页查询时出现数据重复或遗漏。例如，如果按 department_id 排序，多个员工在同一个部门时，这些员工的相对顺序是任意的。

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
ORDER BY department_id, employee_id 
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

使用 FETCH 限制返回行数

查询前 3 条员工信息。

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
ORDER BY employee_id 
FETCH FIRST 3 ROWS ONLY;

使用 OFFSET 跳过指定行数

查询从第 4 行开始的所有员工信息。

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
ORDER BY employee_id 
OFFSET 3 ROWS;

使用 OFFSET 和 FETCH 实现分页

你可以使用 OFFSET 和 FETCH 实现分页查询。

查询第 2 页数据（每页 3 条）：

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
ORDER BY employee_id 
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

使用百分比分页

除了使用固定行数进行分页外，您还可以使用 PERCENT 关键字按百分比返回数据。

查询前 30% 的数据

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
ORDER BY salary 
FETCH FIRST 30 PERCENT ROWS ONLY;

使用 WITH TIES 包含并列行

WITH TIES 选项会在返回指定行数的同时，如果最后一行有并列值，则包含所有并列的行。

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
ORDER BY salary 
FETCH FIRST 3 ROWS WITH TIES;

使用 ROWNUM 进行分页

在 Oracle 模式中，您也可以使用 ROWNUM 伪列进行分页查询，但这种方式相对复杂，需要配合子查询使用。建议您优先使用 Row_Limiting_Clause 进行分页查询，语法更简洁清晰。

使用 ROWNUM 查询前 N 条数据

查询前 3 条员工信息。

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
WHERE ROWNUM <= 3 
ORDER BY employee_id;

使用 ROWNUM 实现分页查询

使用 ROWNUM 实现分页查询需要配合子查询。查询第 2 页（每页 3 条）的数据。

SELECT * FROM (
    SELECT employee_id, employee_name, department_id, salary, ROWNUM rn 
    FROM (
        SELECT employee_id, employee_name, department_id, salary 
        FROM employee_info 
        ORDER BY employee_id
    ) 
    WHERE ROWNUM <= 6
) 
WHERE rn > 3;

相关文档

• 更多有关 SELECT 语法的信息，请参见 SIMPLE SELECT。

• 更多有关单表查询的详细信息，请参见 单表查询。

• 更多有关查询优化的详细信息，请参见 查询改写概述。