roacle order by问题

Question

分页控件的sql语句是这样，分页大小是20
第一页sql
select c.*
  from (select b.*,rownum as rownums               
          from (select a.*
                  from P010B001.V_IB_RECOGNIZE_INFO a                       
                  where  CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd')
                   and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd')
                 order by CreateTime DESC) b
         where rownum <= 20) c
 where rownums >= 1
   and rownums <= 20
结果是
zhqzhqzhq 2009-10-28
北碚万寿都市工业园 2009-7-2
北碚同兴都市工业园 2009-7-1
涪陵北涛乌江都市工业园 2009-6-29
山洞都市工业园 2009-6-26
西永都市工业园 2009-6-25
茅山峡都市工业园 2009-6-25
长寿川染化工都市工业园 2009-6-25
垫江澄溪都市工业园 2009-6-6
大渡口八桥都市工业园 2009-6-6
南岸丹桂都市工业园 2009-6-6
铜梁欧旺都市工业园 2009-6-6
渝中区汇源都市工业园 2009-6-6
潼南县哨楼都市工业园 2009-6-6
九龙坡九里都市工业园 2009-6-6
北碚银钢都市工业园 2009-6-6
江北茂源都市工业园 2009-6-6
江北阿波罗都市工业园 2009-6-6
綦江诚智都市工业楼宇 2009-6-6
南川安坪都市工业园 2009-6-6
第二页sql
select e.*
  from (select d.*,
               rownum as rownums
          from (select *
                  from P010B001.V_IB_RECOGNIZE_INFO
                   where CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd')
                   and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd')
                 order by CreateTime DESC) d
         where rownum <= 40) e
 where rownums >= 21
   and rownums <= 40
结果是
九龙坡九里都市工业园 2009-6-6
北碚银钢都市工业园 2009-6-6
江北茂源都市工业园 2009-6-6
渝中区盛迪亚都市工业楼宇 2009-6-6
江北乾鑫都市工业园 2009-6-6
巴南建金都市工业园 2009-6-6
巴南维阳都市工业园 2009-6-6
南川南城庆岩都市工业园 2009-6-6
江北永腾都市工业园 2009-6-6
南岸白鹤都市工业园 2009-6-6
江北阿波罗都市工业园 2009-6-6
綦江诚智都市工业楼宇 2009-6-6
南川安坪都市工业园 2009-6-6
綦江标王都市工业楼宇 2009-6-6
涪陵鹅颈关都市工业园 2009-6-6
龙塔都市工业楼宇 2009-6-6
南岸皓月都市工业楼宇 2009-6-6
需要说明是CreateTime 这一列，时间用很多是一样的，这样导致了分页后有些数据无法查询出来，如：（璧山西部鞋都都市工业园 2009-6-6）查不出来
所有数据如下
zhqzhqzhq 2009-10-28
北碚万寿都市工业园 2009-7-2
北碚同兴都市工业园 2009-7-1
涪陵北涛乌江都市工业园 2009-6-29
山洞都市工业园 2009-6-26
西永都市工业园 2009-6-25
茅山峡都市工业园 2009-6-25
长寿川染化工都市工业园 2009-6-25
垫江澄溪都市工业园 2009-6-6
大渡口八桥都市工业园 2009-6-6
南岸丹桂都市工业园 2009-6-6
铜梁欧旺都市工业园 2009-6-6
渝中区汇源都市工业园 2009-6-6
潼南县哨楼都市工业园 2009-6-6
巴南先锋都市工业园 2009-6-6
荣昌佳旺都市工业楼宇 2009-6-6
荣昌汇宇都市工业楼园 2009-6-6
璧山西部鞋都都市工业园 2009-6-6
九龙坡海龙都市工业园 2009-6-6
渝中区中安国际都市工业园 2009-6-6
九龙坡九里都市工业园 2009-6-6
北碚银钢都市工业园 2009-6-6
江北茂源都市工业园 2009-6-6
渝中区盛迪亚都市工业楼宇 2009-6-6
江北乾鑫都市工业园 2009-6-6
巴南建金都市工业园 2009-6-6
巴南维阳都市工业园 2009-6-6
南川南城庆岩都市工业园 2009-6-6
江北永腾都市工业园 2009-6-6
南岸白鹤都市工业园 2009-6-6
江北阿波罗都市工业园 2009-6-6
綦江诚智都市工业楼宇 2009-6-6
南川安坪都市工业园 2009-6-6
綦江标王都市工业楼宇 2009-6-6
涪陵鹅颈关都市工业园 2009-6-6
龙塔都市工业楼宇 2009-6-6
南岸皓月都市工业楼宇 2009-6-6
请问如何解决?

Answer 1

全部分页？select t.*
from (
select row_number() over(order by a.CreateTime DESC) rn,
a.*
                from P010B001.V_IB_RECOGNIZE_INFO a
where  CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd')
                and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd')
) t
where t.rn > 1
and t.rn <= 20;

Answer 2

你内层的rownum限制其实只取到了前20条记录，要在排序字段上分页，使用row_number() over(order by a.CreateTime DESC)来取得。

Answer 3

shiyiwan
 对的，我的数据一共只用37条，所以只用17条记录

Answer 4

学习下，好像有个parttition的东东，请教下是否在大数据量的情况下效率更高啊？

Answer 5

--在内容再对名称加个排序.select a.* 
from P010B001.V_IB_RECOGNIZE_INFO a                      
where  CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd') 
and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd') 
order by CreateTime desc, name 

Answer 6

这个确实会出现问题，因为第一次和二次查询排序时 拥有同样时间的记录顺序是随机的。
row_number并不能解决这样的问题。如果排序先按时间排，再按rowid排应该会避免这种情况。
order by createtime desc, rowid;

Answer 7

不会呀，效率没有比较过partition是用于分组的

Answer 8

我看了，这样是可以，请问哈是什么原因了，
select d.*, 
              rownum as rownums 
          from (select * 
                  from P010B001.V_IB_RECOGNIZE_INFO 
                  where CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd') 
                  and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd') 
                order by CreateTime DESC) d 
        where rownum <= 40
与
select d.*, 
              rownum as rownums 
          from (select * 
                  from P010B001.V_IB_RECOGNIZE_INFO 
                  where CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd') 
                  and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd') 
                order by CreateTime DESC) d 
        where rownum <= 20
为什么不同了

Answer 9

order by 加了name让createtime相同的记录不再随机排列，所以就可以了

Answer 10

嗯，可能那里内层用了order by createtime，而createtime精确到了微秒，所以如果去掉createtime用其他的varchar之类的来order by一下，应该就会出现咯。猜的。呵呵。

Answer 11

明天去公司测试一下，这个每次是：
不同的session中执行都是那一条不出来，还是在同一个session里面执行多次？

Answer 12

要是真是随机的话，那
select d.*, 
              rownum as rownums 
          from (select * 
                  from P010B001.V_IB_RECOGNIZE_INFO 
                  where CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd') 
                  and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd') 
                order by CreateTime DESC) d 
        where rownum <= 40 
这个每次顺序应该不一样吧

Answer 13

改成
select c.* 
  from (select a.* ,row_number()over(order by CreateTime DESC)rownums
                  from P010B001.V_IB_RECOGNIZE_INFO a                      
                  where  CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd') 
                  and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd') 
                ) c 
where rownums >= 1 
  and rownums <= 20 
试试，第二页语句一样，改where rownums..后面的数字
看看还会不会出现这种情况
当排序使用的列出现重复的时候，用rownum可能会有一些问题，因为两次排序很可能结果不同。
而且用row_number()代替rownum，本机上测试，前者性能要优于后者

Answer 14

呵呵，我用500万数据测试的结果是：使用rownum来排序，会出现2种结果，然后继续查询，都是那2种结果交替出现。row_number就没有这样的问题。第二天再测试，row_number的排序还和原来相同，而rownum的结果出现了和第一次不同的排序，但还是2种交替出现

Answer 15

我刚刚测试了，
第一页sql
select a.ibname,a.createtime,a.rownums
          from (
                select c.*,row_number()over(order by CreateTime DESC) as rownums 
                  from p010b001.v_ib_recognize_info c                 
                    where CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd')
                     and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd')) a where rownums <= 20
结果如下:
ibname                time             rownums
               
qy00125               2009-10-28 1
重庆市万寿都市工业园 2009-7-2 2
同兴都市工业园        2009-7-1         3
涪陵白涛乌江都市工业园 2009-6-29 4
沙坪坝区山洞都市工业园 2009-6-26 5
重庆西永都市工业园        2009-6-25         6
重庆市茅山峡都市工业园 2009-6-25 7
长寿区川染都市工业园 2009-6-25 8
垫江澄溪都市工业园        2009-6-6          9
八桥科技都市工业园        2009-6-6        10
南岸丹桂都市工业园        2009-6-6       11
铜梁县欧旺都市工业园 2009-6-6 12
渝中区汇源都市工业楼宇 2009-6-6 13
潼南哨楼都市工业园  2009-6-6 14
渝中区中安国际都市工业楼宇 2009-6-6 15
重庆市九龙坡区九里都市工业园 2009-6-6 16
重庆银钢都市工业园          2009-6-6         17
重庆市茂源都市工业园 2009-6-6 18
重庆市阿波罗都市工业园 2009-6-6 19
重庆市诚智都市工业园 2009-6-6 20
第二页sql
select a.ibname,a.createtime,a.rownums
          from (
                select c.*,row_number()over(order by CreateTime DESC) as rownums 
                  from p010b001.v_ib_recognize_info c                 
                    where CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd')
                     and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd')) a where rownums <= 40 and rownums>=21
结果如下：重庆市九龙坡区九里都市工业园 2009-6-6 21
重庆银钢都市工业园 2009-6-6 22
重庆市茂源都市工业园 2009-6-6 23
渝中区盛迪亚都市工业楼宇 2009-6-6 24
重庆市乾鑫都市工业园 2009-6-6 25
巴南区建金都市工业园 2009-6-6 26
重庆市维阳食品工业园 2009-6-6 27
南川区南城庆岩都市工业园 2009-6-6 28
重庆市永腾都市工业园 2009-6-6 29
南岸白鹤都市工业园 2009-6-6 30
重庆市阿波罗都市工业园 2009-6-6 31
重庆市诚智都市工业园 2009-6-6 32
南川区安坪都市工业园 2009-6-6 33
标王都市工业园 2009-6-6 34
涪陵鹅颈关都市工业园 2009-6-6 35
重庆龙塔都市工业楼宇 2009-6-6 36
重庆皓月都市工业楼宇 2009-6-6 37
结果还是不对头，比如（重庆市茂源都市工业园 2009-6-6 18
有重复，璧山西部鞋都都市工业园 2009-6-6 还是没出来）

Answer 16

其实这样是可以的
sql1：
select *
  from (select a.*, rownum as rownums
          from (select *
                  from p010b001.v_ib_recognize_info c                 
                 where CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd')
                   and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd') order by c.CREATETIME desc) a
        )
 where rownums <=20
结果:
qy00125 2009-10-28
重庆市万寿都市工业园 2009-7-2
同兴都市工业园 2009-7-1
涪陵白涛乌江都市工业园 2009-6-29
沙坪坝区山洞都市工业园 2009-6-26
重庆西永都市工业园 2009-6-25
重庆市茅山峡都市工业园 2009-6-25
长寿区川染都市工业园 2009-6-25
垫江澄溪都市工业园 2009-6-6
八桥科技都市工业园 2009-6-6
南岸丹桂都市工业园 2009-6-6
铜梁县欧旺都市工业园 2009-6-6
渝中区汇源都市工业楼宇 2009-6-6
潼南哨楼都市工业园 2009-6-6
重庆市先锋都市工业园 2009-6-6
荣昌佳旺都市工业楼宇 2009-6-6
荣昌汇宇都市工业园 2009-6-6
重庆奥康都市工业楼宇 2009-6-6
九龙坡海龙都市工业园 2009-6-6
渝中区中安国际都市工业楼宇 2009-6-6
sql2:
select ibname,CREATETIME
  from (select a.*, rownum as rownums
          from (select *
                  from p010b001.v_ib_recognize_info c                 
                 where CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd')
                   and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd') order by c.CREATETIME desc) a
        )
 where rownums <= 40
   and rownums >= 21
结果：
重庆市九龙坡区九里都市工业园 2009-6-6
重庆银钢都市工业园 2009-6-6
重庆市茂源都市工业园 2009-6-6
渝中区盛迪亚都市工业楼宇 2009-6-6
重庆市乾鑫都市工业园 2009-6-6
巴南区建金都市工业园 2009-6-6
重庆市维阳食品工业园 2009-6-6
南川区南城庆岩都市工业园 2009-6-6
重庆市永腾都市工业园 2009-6-6
南岸白鹤都市工业园 2009-6-6
重庆市阿波罗都市工业园 2009-6-6
重庆市诚智都市工业园 2009-6-6
南川区安坪都市工业园 2009-6-6
标王都市工业园 2009-6-6
涪陵鹅颈关都市工业园 2009-6-6
重庆龙塔都市工业楼宇 2009-6-6
重庆皓月都市工业楼宇 2009-6-6
也就是在内层不指定 rownum

Answer 17

select e.* 
  from (select d.*
          from (select * 
                  from P010B001.V_IB_RECOGNIZE_INFO 
                  where CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd') 
                  and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd') 
                order by CreateTime DESC) d 
        where rownum <= 40) e 
where rownums >= 21 
  and rownums <= 40;这样是正确的吗，就是吧d.*后面的rownum去掉

Answer 18

这样肯定不行啊，rownums就没定义了，我是说去掉rownum<范围，这个去掉，可以
问题就出在 where  rownum<范围

Answer 19

select ibname,CREATETIME 
  from (select a.*, rownum as rownums 
          from (select * 
                  from p010b001.v_ib_recognize_info c                
                where CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd') 
                  and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd') order by c.CREATETIME desc) a 
        ) 
where rownums <= 40 
  and rownums >= 21

Answer 20

select ibname,CREATETIME 
  from (select a.*, rownum as rownums 
          from (select * 
                  from p010b001.v_ib_recognize_info c                
                where CreateTime >= to_date('2009-1-1', 'yyyy-MM-dd') 
                  and CreateTime < to_date('2010-1-1', 'yyyy-MM-dd') order by c.CREATETIME desc) a 
        ) 
where rownums <= 20

Answer 21

http://topic.csdn.net/u/20091109/12/2078b0a5-89ba-4c29-bec8-565dd1773287.html之前我发过贴讨论ROWNUM 与ORDERY BY 有并列数据的问题，你可以参考一下

Answer 22

确切说没得到答案，我在ITPUB也发过贴子，倒底怎么回事，没有人回答出来，都只是提了些解决办法