EclipseLink分页和Oracle ROWNUM

Question

使用带有规范和分页的Eclipse Link JPA 2.5.2，我对Oracle ROWNUM的执行方式有问题。Eclipse Link生成的查询如下：

  SELECT *
  FROM (SELECT a.*,
               ROWNUM rnum
          FROM (SELECT   t0.ID,
                         t0.participantidentity_id,
                         t0.submitteddate
                    FROM delegation t4,
                         meeting t3,
                         meetinggroup t2,
                         participantidentity t1,
                         reimbursement t0,
                         userdata ud
                   WHERE t0.current_id IS NULL
                     AND t1.activeparticipant_id IS NOT NULL
                     AND t2.responsibleservice_id IN (74)
                     AND t1.ID = t0.participantidentity_id
                     AND t4.ID = t1.delegation_id
                     AND t3.ID = t4.meeting_id
                     AND t2.ID = t3.meetinggroup_id
                     AND t1.userdata_id = ud.ID
                ORDER BY t0.submitteddate ASC) a
         WHERE ROWNUM <= 25)
 WHERE rnum > 0

为第一页运行该查询应返回0到25之间的行，为第二页运行相同的查询应返回25到50之间的行。

问题是第一页的记录也出现在第二页，我猜是因为submitteddate可以包含NULL值并且ORDER BY子句在ROWNUM和rnum条件或类似的东西之前没有应用，我不确定，因为我现在有点困惑...

谁能解释一下原因吗？

谢啦！

Answer 1

我根据上面的场景做了一些测试--

CREATE TABLE T3(AA NUMBER(10),BB NUMBER(10),CC NUMBER(10),DD NUMBER(10));
INSERT INTO T3 VALUES (1,2,3,null);
INSERT INTO T3 VALUES (4,5,6,13);
INSERT INTO T3 VALUES (7,8,9,NULL);
INSERT INTO T3 VALUES (10,11,12,14);

然后--

 SELECT *
FROM
  (SELECT
    a.*,
    rownum rnum
  FROM
    (SELECT aa AS a1,
      bb   AS a2,
      CC          AS A3,
      dd as a4
    FROM T3
    ORDER BY DD ASC) A
  WHERE ROWNUM <= 2
  )
WHERE rnum > 0;

此测试永远不会返回重复项。

所以请执行您下面的查询

SELECT   t0.ID,
                             t0.participantidentity_id,
                             t0.submitteddate
                        FROM delegation t4,
                             meeting t3,
                             meetinggroup t2,
                             participantidentity t1,
                             reimbursement t0,
                             userdata ud
                       WHERE t0.current_id IS NULL
                         AND t1.activeparticipant_id IS NOT NULL
                         AND t2.responsibleservice_id IN (74)
                         AND t1.ID = t0.participantidentity_id
                         AND t4.ID = t1.delegation_id
                         AND t3.ID = t4.meeting_id
                         AND t2.ID = t3.meetinggroup_id
                         AND t1.userdata_id = ud.ID
                    ORDER BY t0.submitteddate ASC

并检查是否有任何副本。

此外，尝试在sql开头使用distinct关键字，如选择distinct t0。ID