oracle 排序分页混乱

sql如下：

Sql代码

1. select * 
2.   from (select id, member_id, company_name, rownum rn 
3.           from (select t.id, t.member_id, t.company_name 
4.                   from table1 t, table2 d 
5.                  where t.id = d.opp_id(+) 
6.                    and product_id_prefer = 1 
7.                    and t.sales_id = 'sunny.zhaoy' 
8.                  order by t.maturity asc, d.memo) 
9.          where rownum <= #endrow#) m 
10. where m.rn >= #rowid# 
11.  
12. 这个sql是正确的，在开发库用了下，没有问题、、但是提交到测试库就有问题了，第三页和第四页，第五页第六页 怎么查询都是一样的结果。 
13.  
14. 比如我一页50条，那么下面两个sql查询的结果居然一模一样（总共600条）： 
15.  
16.  
17. -------------------------------------------------------------------------------- 
18.  
19. select * 
20.   from (select id, member_id, company_name, rownum rn 
21.           from (select t.id, t.member_id, t.company_name 
22.                   from table1 t, table2 d 
23.                  where t.id = d.opp_id(+) 
24.                    and product_id_prefer = 1 
25.                    and t.sales_id = 'sunny.zhaoy' 
26.                  order by t.maturity asc, d.memo) 
27.          where rownum <= 150) m 
28. where m.rn >= 101 
29.   
30. ====================================================== 
31.   
32. select * 
33.   from (select id, member_id, company_name, rownum rn 
34.           from (select t.id, t.member_id, t.company_name 
35.                   from table1 t, table2 d 
36.                  where t.id = d.opp_id(+) 
37.                    and product_id_prefer = 1 
38.                    and t.sales_id = 'sunny.zhaoy' 
39.                  order by t.maturity asc, d.memo) 
40.          where rownum <= 200) m 
41. where m.rn >= 151 
42.  
43.  
44.  
45. -------------------------------------------------------------------------------- 
46.  
47.  
48. 基本上应该没啥问题，问题出现在什么地方呢？ 
49.  
50. 首先这跟oracle无关, 是典型的order by字段值不唯一造成分页记录混乱。 
51.  
52. 也就是说 我用 t.maturity asc, d.memo 排序，但是这两个字段的值可能都是相同的，或者很多都是不唯一的。。这时候就会出现这个问题了。 
53.  
54. 怎么解决呢？ 
55.  
56. 其实很简单，只要在排序的时候加一个值唯一的字段的就可以了。最好是id 
57.  
58. 改成如下就ok了： 
59.  
60. select * 
61.   from (select id, member_id, company_name, rownum rn 
62.           from (select t.id, t.member_id, t.company_name 
63.                   from table1 t, table2 d 
64.                  where t.id = d.opp_id(+) 
65.                    and product_id_prefer = 1 
66.                    and t.sales_id = 'sunny.zhaoy' 
67.                  order by t.maturity asc, d.memo,t.id) 
68.          where rownum <= 150) m 
69. where m.rn >= 101 
70.   
71. ====================================================== 
72.   
73. select * 
74.   from (select id, member_id, company_name, rownum rn 
75.           from (select t.id, t.member_id, t.company_name 
76.                   from table1 t, table2 d 
77.                  where t.id = d.opp_id(+) 
78.                    and product_id_prefer = 1 
79.                    and t.sales_id = 'sunny.zhaoy' 
80.                  order by t.maturity asc, d.memo,t.id) 
81.          where rownum <= 200) m 
82. where m.rn >= 151