标题翻译
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(
"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 > (#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_CLASUE#", paginationCriteria.getOrderByClause())
.replaceAll("#PAGE_NUMBER#", paginationCriteria.getPageNumber().toString())
.replaceAll("#PAGE_SIZE#", 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 > (:PAGE * 5) AND RN <= (: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?
专注分享java语言的经验与见解，让所有开发者获益！
评论