在ORACLE中对复杂的连接查询使用OFFSET和LIMIT？

10 浏览
0 Comments

在ORACLE中对复杂的连接查询使用OFFSET和LIMIT？

我正在使用Oracle 11g，并且有一个复杂的联接查询。在这个查询中，我真的想要应用OFFSET和LIMIT，以便在Spring Batch Framework中有效地使用。

我查阅了以下内容：

如何在Oracle查询中排序后限制返回的行数？ 和

Oracle分页的LIMIT和OFFSET的替代方案

但是对我来说仍然不太清楚。

我的查询：

SELECT DEPT.ID rowobjid, DEPT.CREATOR createdby, DEPT.CREATE_DATE createddate, DEPT.UPDATED_BY updatedby, DEPT.LAST_UPDATE_DATE updateddate, 
DEPT.NAME name, DEPT.STATUS status, statusT.DESCR statusdesc, 
REL.ROWID_DEPT1 rowidDEPT1, REL.ROWID_DEPT2 rowidDEPT2, DEPT2.DEPT_FROM_VAL parentcid, DEPT2.NAME parentname 
FROM TEST.DEPT_TABLE DEPT 
LEFT JOIN TEST.STATUS_TABLE statusT ON DEPT.STATUS = statusT.STATUS 
LEFT JOIN TEST.C_REL_DEPT rel ON DEPT.ID=REL.ROWID_DEPT2 
LEFT JOIN TEST.DEPT_TABLE DEPT2 ON REL.ROWID_DEPT1=DEPT2.ID
ORDER BY rowobjid asc;

上述查询给我返回了1000万条记录。

注意：数据库表都没有主键，所以我需要使用OFFSET和LIMIT。

0

1 答案

0 Comments

问题的出现原因：

问题的出现是因为在Oracle的复杂连接查询中，需要使用OFFSET和LIMIT逻辑，但是这些逻辑只在12c及以后的版本中提供。因此，需要找到一种解决方法来实现相同的功能。

解决方法：

可以在Oracle 11g中使用分析函数，例如ROW_NUMBER()，在子查询中使用。假设需要获取按CREATE_DATE分组并按部门ID排序的第3到第8行，以实现OFFSET 3 LIMIT 8的逻辑。以下是解决方法的示例代码：

SELECT q.*
FROM (
  SELECT DEPT.ID rowobjid,
         DEPT.CREATOR createdby,
         DEPT.CREATE_DATE createddate,
         DEPT.UPDATED_BY updatedby,
         DEPT.LAST_UPDATE_DATE updateddate,
         DEPT.NAME name,
         DEPT.STATUS status,
         statusT.DESCR statusdesc,
         REL.ROWID_DEPT1 rowidDEPT1,
         REL.ROWID_DEPT2 rowidDEPT2,
         DEPT2.DEPT_FROM_VAL parentcid,
         DEPT2.NAME parentname,
         ROW_NUMBER() OVER (PARTITION BY DEPT.CREATE_DATE ORDER BY DEPT.ID) AS rn
  FROM TEST.DEPT_TABLE DEPT
  LEFT JOIN TEST.STATUS_TABLE statusT
    ON DEPT.STATUS = statusT.STATUS
  LEFT JOIN TEST.C_REL_DEPT rel
    ON DEPT.ID = REL.ROWID_DEPT2
  LEFT JOIN TEST.DEPT_TABLE DEPT2
    ON REL.ROWID_DEPT1 = DEPT2.ID
) q
WHERE rn BETWEEN 3 AND 8;

以上代码将返回恰好6行结果。如果需要包括相同CREATE_DATE下部门身份标识的并列行（即并列的行），则应将ROW_NUMBER()替换为另一个窗口函数DENSE_RANK()，其余查询的其他部分保持不变。在这种情况下，至少会返回6条记录。

关于如何在Spring Batch的分区中找到最小值和最大值的行数：

在这种情况下，可以考虑在主查询中过滤掉rn = 1。当按照dept.id升序排序时，可以得到最小值，或者按照降序排序时，可以得到最大值的dept.id和返回结果的对应部分。如果您需要学习这一点，请告诉我。

0