使用where和offset fetch子句的简单选择在oracle中不起作用

小编典典

使用where和offset fetch子句的简单选择在oracle中不起作用

sql

我正在尝试选择where有条件的行，并且需要分页。所以我添加Fetch了offset[使其动态化]子句，但随后出现此错误：

ORA-00933：SQL命令未正确结束位置：414

我的查询：

SELECT
    up.NAME upozilaName_bn,
       up.id upozila,
    dis.NAME districtName_bn,
       dis.id district,
    dv.NAME divisionName_bn,
       dv.id division,
    w.COUNTER_TYPE, w.COUNTER_ID,
    w.STATUS
FROM X w
left join  Y up
    ON w.UPOZILA = up.ID
left JOIN Z dis
    ON w.DISTRICT = dis.id
left join  P dv
    ON w.DIVISION = dv.ID
where 1=1  order by upozilaName_bn asc OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY;

没有FFSET 1 ROWS FETCH NEXT 10 ROWS ONLY这个，就可以了！我搜索了该错误：933，但未找到任何帮助。Oracle版本：11.2g

阅读 244

2021-04-15

共1个答案

小编典典

OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY 可从Oracle 12c获得。

相反，您需要执行查询并对数据进行排序。然后为有序行生成一个行号；最后过滤这些行号。这些步骤需要以正确的顺序在嵌套子查询中进行：

SELECT *
FROM   (
  SELECT t.*,
         ROWNUM AS rn
  FROM   (
    SELECT up.NAME AS upozilaName_bn,
           up.id AS upozila,
           dis.NAME AS districtName_bn,
           dis.id AS district,
           dv.NAME AS divisionName_bn,
           dv.id AS division,
           w.COUNTER_TYPE,
           w.COUNTER_ID,
           w.STATUS
    FROM X w
    left join  Y up ON w.UPOZILA = up.ID
    left JOIN  Z dis ON w.DISTRICT = dis.id
    left join  P dv ON w.DIVISION = dv.ID
    order by upozilaName_bn asc
  ) T
)
WHERE  rn BETWEEN 2 AND 11;

2021-04-15