注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

常在心

淡泊明志,人生自在

 
 
 

日志

 
 

rownum引起的排序结果问题  

2011-03-28 14:21:41|  分类: oracle开发 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

rownum引起的排序结果问题:

改变前的语句:
--------------------------------------------------------------------------------------------------------
SELECT *
  FROM (SELECT PS.ID,
               PS.NEXTCONNECTDURATION,
               PS.MOREGAMESID,
               PS.NEXTPAGEID,
               PS.USERTYPEID,
               PS.DEFAULTGAMEID,
               PS.ENABLEMOREGAMES
          FROM BLADE2.PRODUCT_STATUS PS, BLADE2.USER_TYPE UT
         WHERE PS.TYPE = 1
           AND UT.TYPENO = 2
           AND PS.USERTYPEID = UT.ID
           AND UT.USEDTIME <= 9
           AND UT.USEDRATE <= 3
           AND UT.IMSITIME <= 9
           AND UT.IMSIRATE <= 3
           AND PS.ID IN (SELECT PBG.PRODUCTSTATUSID
                           FROM BLADE2.PS_BIND_GAME PBG
                          WHERE PBG.GAMEID = 100001)
           AND ((UT.MOBILEFACTORYID IN
               (SELECT MF.ID
                    FROM BLADE2.MOBILE_FACTORY MF
                   WHERE MF.CODE = '156') AND
               UT.MOBILEFACTORYCHILDID IS NULL) OR
               (UT.MOBILEFACTORYID IS NULL AND
               UT.MOBILEFACTORYCHILDID IS NULL))
         ORDER BY UT.MOBILEFACTORYCHILDID ASC,
                 
                  UT.IMSITIME DESC,
                  UT.IMSIRATE DESC,
                  UT.USEDTIME DESC,
                  UT.USEDRATE DESC)
 WHERE ROWNUM <= 2;
-------------------------------------------------------------------------
当用下面的语句的时候会结果是:
SELECT PS.ID,
               PS.NEXTCONNECTDURATION,
               PS.MOREGAMESID,
               PS.NEXTPAGEID,
               PS.USERTYPEID,
               PS.DEFAULTGAMEID,
               PS.ENABLEMOREGAMES
          FROM BLADE2.PRODUCT_STATUS PS, BLADE2.USER_TYPE UT
         WHERE PS.TYPE = 1
           AND UT.TYPENO = 2
           AND PS.USERTYPEID = UT.ID
           AND UT.USEDTIME <= 9
           AND UT.USEDRATE <= 3
           AND UT.IMSITIME <= 9
           AND UT.IMSIRATE <= 3
           AND PS.ID IN (SELECT PBG.PRODUCTSTATUSID
                           FROM BLADE2.PS_BIND_GAME PBG
                          WHERE PBG.GAMEID = 100001)
           AND ((UT.MOBILEFACTORYID IN
               (SELECT MF.ID
                    FROM BLADE2.MOBILE_FACTORY MF
                   WHERE MF.CODE = '156') AND
               UT.MOBILEFACTORYCHILDID IS NULL) OR
               (UT.MOBILEFACTORYID IS NULL AND
               UT.MOBILEFACTORYCHILDID IS NULL))
         ORDER BY UT.MOBILEFACTORYCHILDID ASC,                 
                  UT.IMSITIME DESC,
                  UT.IMSIRATE DESC,
                  UT.USEDTIME DESC,
                  UT.USEDRATE DESC

结果:
ID NEXTCONNECTDURATION MOREGAMESID NEXTPAGEID USERTYPEID DEFAULTGAMEID ENABLEMOREGAMES
232   12  7  8  137  3 
243   1  2  3  130  3 

用最上面的语句结果如下:
ID NEXTCONNECTDURATION MOREGAMESID NEXTPAGEID USERTYPEID DEFAULTGAMEID ENABLEMOREGAMES
243   1  2  3  130  3 
232   12  7  8  137  3 

排序的就不一样,这就是rownum的机制造成的,因为rownum是伪列,它的特点是按顺序标记,而且是逐次递加的,由于select *括号里面的是按MOBILEFACTORYCHILDID字段按升序的,所以子查询的时候看到的升序的,但外面使用rownum的时候是按oracle默认的第一个字段ID升序排序,故显示的结果就不是我们想要的,但如果在子查询中直接显示先把字段调用了,就不会有这种情况了。改写如下:
SELECT *
  FROM (SELECT ROWNUM RN,PS.ID,
               PS.NEXTCONNECTDURATION,
               PS.MOREGAMESID,
               PS.NEXTPAGEID,
               PS.USERTYPEID,
               PS.DEFAULTGAMEID,
               PS.ENABLEMOREGAMES
          FROM BLADE2.PRODUCT_STATUS PS, BLADE2.USER_TYPE UT
         WHERE PS.TYPE = 1
           AND UT.TYPENO = 2
           AND PS.USERTYPEID = UT.ID
           AND UT.USEDTIME <= 9
           AND UT.USEDRATE <= 3
           AND UT.IMSITIME <= 9
           AND UT.IMSIRATE <= 3
           AND PS.ID IN (SELECT PBG.PRODUCTSTATUSID
                           FROM BLADE2.PS_BIND_GAME PBG
                          WHERE PBG.GAMEID = 100001)
           AND ((UT.MOBILEFACTORYID IN
               (SELECT MF.ID
                    FROM BLADE2.MOBILE_FACTORY MF
                   WHERE MF.CODE = '156') AND
               UT.MOBILEFACTORYCHILDID IS NULL) OR
               (UT.MOBILEFACTORYID IS NULL AND
               UT.MOBILEFACTORYCHILDID IS NULL))
         ORDER BY UT.MOBILEFACTORYCHILDID ASC,
                  UT.IMSITIME             DESC,
                  UT.IMSIRATE             DESC,
                  UT.USEDTIME             DESC,
                  UT.USEDRATE             DESC) tt
 WHERE rn <= 2;

  评论这张
 
阅读(99)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017