今天有个需求用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