Oracle分页SQL报错ORA-00900及分页结果异常问题求助

你的SQL报错ORA-00900是因为外层查询连续写了3个WHERE子句，Oracle语法不允许一个SELECT语句存在多个WHERE关键字，这是直接的语法错误。同时分页逻辑也存在问题：外层的ROWNUM是重新计算的，和内层生成的r__行号逻辑冲突，导致分页结果异常。

以下是修正语法错误、调整分页逻辑后的完整语句：

SELECT *
FROM (
  SELECT b.*,
         ROWNUM r__
  FROM (
    SELECT a.KODE_KLAIM,
           a.NO_SS,
           a.LA,
           a.NAMA_TK,
           a.KODE_K,
           tk1.TEM_LAHIR,
           TO_CHAR(tk2.TLAHIR, 'DD/MM/RRRR') TLAHIR
    FROM PN.KLAIM a
    -- 用LEFT JOIN替代关联子查询，提升性能并避免重复扫描
    LEFT JOIN (
      SELECT KODE_K, TEM_LAHIR
      FROM KN.VW_KN_TK
      WHERE ROWNUM = 1 -- 取每个KODE_K的第一条记录，若需稳定排序可加ORDER BY
    ) tk1 ON tk1.KODE_K = a.KODE_K
    LEFT JOIN (
      SELECT KODE_K, TLAHIR
      FROM KN.VW_KTK
      WHERE ROWNUM = 1
    ) tk2 ON tk2.KODE_K = a.KODE_K
    WHERE NVL(a.STATUS_BATAL,'X') = 'T'
      AND A.NOMOR IS NOT NULL
      AND A.TIPE_KLAIM = 'JPN01'
    -- 若需要分页结果稳定，必须在此处加ORDER BY，否则分页可能出现重复/缺失数据
    -- ORDER BY a.KODE_KLAIM
  ) b
)
WHERE r__ BETWEEN ((? - 1) * ?) + 1 AND (? * ?)

参数顺序对应：

• 第一个?：页码（pageNum），比如第1页传1，第2页传2
• 第二个?：每页条数（pageSize），比如每页10条传10
• 第三个?：页码（pageNum）
• 第四个?：每页条数（pageSize）

如果你的客户端支持绑定变量名，可简化参数复用：

WHERE r__ BETWEEN ((:pageNum - 1) * :pageSize) + 1 AND (:pageNum * :pageSize)

1. 语法修正：删除冗余的where 1=1，将多个WHERE合并为一个，用AND连接条件
2. 分页逻辑正确性：依赖内层生成的r__行号做范围过滤，避开ROWNUM的特性陷阱（ROWNUM是在结果集生成后逐行分配的，直接过滤ROWNUM > N会返回空）
3. 子查询优化：把关联子查询改为LEFT JOIN，减少重复查询同一张视图的开销，提升性能
4. 结果稳定性：如果需要分页结果不随数据库执行计划变化而乱序，必须在最内层的b子查询中添加ORDER BY子句，比如按KODE_KLAIM排序

内容的提问来源于stack exchange，提问作者loops