分页自定义查询获取前 N 行错误 [英] Pagination custom query fetch first N rows error

查看:61
本文介绍了分页自定义查询获取前 N 行错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在 Oracle 10g 中有这么长的查询(需要自定义分页).

I have such a long query like this in Oracle 10g (needed for custom pagination).

SELECT *
  FROM (
       SELECT FILTERED_ORDERED_RESULTS.*,
              COUNT(1) OVER() TOTAL_RECORDS
         FROM (
              SELECT BASEINFO.*,
                     ROWNUM AS RN
                FROM (
                     SELECT A.ID_LIST            AS ID,
                            A.NAME,
                            A.DATE_CREATE        AS DATECREATE,
                            A.DATE_UPDATE        AS DATEUPDATE,
                            A.USER_ID            AS USERID,
                            A.TYPE,
                            NVL(
                                   B.CNT, 0
                            )        CNT
                       FROM MAP_S_LIST_ARTS                                                           A
                       LEFT JOIN (
                            SELECT ID_LIST,
                                   COUNT(*) CNT
                              FROM MAP_LIST_ARTS
                             GROUP BY ID_LIST
                     )        B ON A.ID_LIST = B.ID_LIST
                      ORDER BY A.ID_LIST DESC
              ) BASEINFO
       ) FILTERED_ORDERED_RESULTS
        ORDER BY CNT DESC
)
 WHERE RN > (:PAGE * 5) AND RN <= (:PAGE + 1) * 5 

现在我有这种行为......我没有在所有数据中得到正确的排序,只有在指定的 rn 范围内,并且出于某种原因 rn 不想改变......所以我得到了大量的过滤

Now I have this behavior... I don't get the correct ordering throughout all the data, only inside the rn range specified and for some reason rn doesn't want to change... So i get onluy chunks of filtering

推荐答案

FETCH FIRST N ROWS 是 Oracle 12c 及更高版本支持的新 SQL 语法.Oracle 10g(您标记的内容)不支持此功能,您必须使用类似

FETCH FIRST N ROWS is a new SQL syntax supported in Oracle 12c and higher. Oracle 10g (what you tagged) does not support this, you would have to use something like

WHERE ROWNUM < 6

请使用 Oracle 数据库的确切版本和实际错误消息更新您的问题,以便我们确定您的问题是什么.

Please update your question with exact version of Oracle Database and the actual error message so we can know for sure what your issue is.

这篇关于分页自定义查询获取前 N 行错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆