在Oracle SQL中正确排序分页结果。

huangapple 未分类评论50阅读模式
标题翻译

Correctly sorting paginated results in oracle SQL

问题

我有一个数据表服务器端分页的实现。查询构建器如下:

public static String buildPaginatedQueryForOracle(String baseQuery, PaginationCriteria paginationCriteria) {

    StringBuilder sb = new StringBuilder(
        "SELECT * FROM (SELECT FILTERED_ORDERED_RESULTS.*, COUNT(1) OVER() total_records, ROWNUM AS RN FROM (SELECT BASEINFO.* FROM ( #BASE_QUERY# ) BASEINFO ) FILTERED_ORDERED_RESULTS #WHERE_CLAUSE# #ORDER_CLAUSE# ) WHERE RN > (#PAGE_NUMBER# * #PAGE_SIZE#) AND RN <= (#PAGE_NUMBER# + 1) * #PAGE_SIZE# ");
    String finalQuery = null;

    int pageNo = paginationCriteria.getPageNumber() / paginationCriteria.getPageSize();
    paginationCriteria.setPageNumber(pageNo);

    if (!AppUtil.isObjectEmpty(paginationCriteria)) {
        finalQuery = sb.toString().replaceAll("#BASE_QUERY#", baseQuery)
            .replaceAll("#WHERE_CLAUSE#",
                ((AppUtil.isObjectEmpty(paginationCriteria.getFilterByClause())) ? "" : " WHERE ")
                    + paginationCriteria.getFilterByClause())
            .replaceAll("#ORDER_CLAUSE#", paginationCriteria.getOrderByClause())
            .replaceAll("#PAGE_NUMBER#", paginationCriteria.getPageNumber().toString())
            .replaceAll("#PAGE_SIZE#", paginationCriteria.getPageSize().toString());
    }
    return (null == finalQuery) ? baseQuery : finalQuery;
}

其中 base_query 是来自 dao-impl 层的常规字符串。它很好地完成了工作。但是,当我想要按照 cnt 在排序子句中进行排序时(这个特定的情况使用 cnt 来计算与第一个表中的每一行关联的另一个表中的行数,如下所示)

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 

我得到的结果是,它首先在另一种排序中(默认情况下是按照 id)选择前 5 行,然后仅对它们应用按 cnt 排序。例如,如果我获取一个包含 5 行的页面,它只会在这些行之间进行排序,尽管我有其他页面。我应该怎么做?

英文翻译

I have an implementation of datatables serverside pagination.The query builder looks like this:

public static String buildPaginatedQueryForOracle(String baseQuery, PaginationCriteria paginationCriteria) {

		StringBuilder sb = new StringBuilder(
				&quot;SELECT * FROM (SELECT FILTERED_ORDERED_RESULTS.*, COUNT(1) OVER() total_records, ROWNUM AS RN FROM (SELECT BASEINFO.* FROM ( #BASE_QUERY# ) BASEINFO ) FILTERED_ORDERED_RESULTS #WHERE_CLAUSE# #ORDER_CLASUE# ) WHERE RN &gt; (#PAGE_NUMBER# * #PAGE_SIZE#) AND RN &lt;= (#PAGE_NUMBER# + 1) * #PAGE_SIZE# &quot;);
		String finalQuery = null;

		int pageNo = paginationCriteria.getPageNumber() / paginationCriteria.getPageSize();
		paginationCriteria.setPageNumber(pageNo);

		if (!AppUtil.isObjectEmpty(paginationCriteria)) {
			finalQuery = sb.toString().replaceAll(&quot;#BASE_QUERY#&quot;, baseQuery)
					.replaceAll(&quot;#WHERE_CLAUSE#&quot;,
							((AppUtil.isObjectEmpty(paginationCriteria.getFilterByClause())) ? &quot;&quot; : &quot; WHERE &quot;)
									+ paginationCriteria.getFilterByClause())
					.replaceAll(&quot;#ORDER_CLASUE#&quot;, paginationCriteria.getOrderByClause())
					.replaceAll(&quot;#PAGE_NUMBER#&quot;, paginationCriteria.getPageNumber().toString())
					.replaceAll(&quot;#PAGE_SIZE#&quot;, paginationCriteria.getPageSize().toString());
		}
		return (null == finalQuery) ? baseQuery : finalQuery;
	}

where base_query is the usual string from the dao-impl layer.
It does the job well. However, when I want to sort the results by cnt in the order clause ( this particular case uses the cnt to count the rows of another table linked to every row in the first table like this)

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 &gt; (:PAGE * 5) AND RN &lt;= (:PAGE + 1) * 5 

What I get is that it first takes first 5 rows in another sorting (the default is by id) and then applies the sorting by cnt to them only. for example, if I fetch a page with 5 rows it will sort only among them though I have other pages. What can I do?

huangapple
  • 本文由 发表于 2020年3月17日 02:02:56
  • 转载请务必保留本文链接:https://java.coder-hub.com/60711042.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定