oracle 排序分页混乱

怼烎@ 2022-08-07 15:00 268阅读 0赞

sql如下：

Sql代码 [ ![复制代码][icon_copy.gif]][_icon_copy.gif]  ![收藏代码][icon_star.png]![spinner.gif][]

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

[icon_copy.gif]: http://qyongkang.iteye.com/images/icon_copy.gif
[_icon_copy.gif]: http://qyongkang.iteye.com/blog/865609#
[icon_star.png]: http://qyongkang.iteye.com/images/icon_star.png
[spinner.gif]: /images/20220805/6e51101050614ec39080757f10904aa3.png