oracle 排序分页混乱

---

今天有个需求用oracle排序分页：

sql如下：

 

select *
  from (select id, member_id, company_name, rownum rn
          from (select t.id, t.member_id, t.company_name
                  from table1 t, table2 d
                 where t.id = d.opp_id(+)
                   and product_id_prefer = 1
                   and t.sales_id = 'sunny.zhaoy'
                 order by t.maturity asc, d.memo)
         where rownum <= #endrow#) m
 where m.rn >= #rowid#

 

这个sql是正确的，在开发库用了下，没有问题、、但是提交到测试库就有问题了，第三页和第四页，第五页第六页 怎么查询都是一样的结果。

比如我一页50条，那么下面两个sql查询的结果居然一模一样（总共600条）：

---

 

select *
  from (select id, member_id, company_name, rownum rn
          from (select t.id, t.member_id, t.company_name
                  from table1 t, table2 d
                 where t.id = d.opp_id(+)
                   and product_id_prefer = 1
                   and t.sales_id = 'sunny.zhaoy'
                 order by t.maturity asc, d.memo)
         where rownum <= 150) m
 where m.rn >= 101
 
 ======================================================
 
 select *
  from (select id, member_id, company_name, rownum rn
          from (select t.id, t.member_id, t.company_name
                  from table1 t, table2 d
                 where t.id = d.opp_id(+)
                   and product_id_prefer = 1
                   and t.sales_id = 'sunny.zhaoy'
                 order by t.maturity asc, d.memo)
         where rownum <= 200) m
 where m.rn >= 151

 

---

 

基本上应该没啥问题，问题出现在什么地方呢？

首先这跟oracle无关, 是典型的order by字段值不唯一造成分页记录混乱。

也就是说 我用 t.maturity asc, d.memo 排序，但是这两个字段的值可能都是相同的，或者很多都是不唯一的。。这时候就会出现这个问题了。

怎么解决呢？

其实很简单，只要在排序的时候加一个值唯一的字段的就可以了。最好是id

 

改成如下就ok了：

 

select *
  from (select id, member_id, company_name, rownum rn
          from (select t.id, t.member_id, t.company_name
                  from table1 t, table2 d
                 where t.id = d.opp_id(+)
                   and product_id_prefer = 1
                   and t.sales_id = 'sunny.zhaoy'
                 order by t.maturity asc, d.memo,t.id)
         where rownum <= 150) m
 where m.rn >= 101
 
 ======================================================
 
 select *
  from (select id, member_id, company_name, rownum rn
          from (select t.id, t.member_id, t.company_name
                  from table1 t, table2 d
                 where t.id = d.opp_id(+)
                   and product_id_prefer = 1
                   and t.sales_id = 'sunny.zhaoy'
                 order by t.maturity asc, d.memo,t.id)
         where rownum <= 200) m
 where m.rn >= 151