java技術研究

關于翻頁的sql結果集不確定問題

今天碰見一個問題：
sql-1：
SELECT temp.*, ROWNUM num
          FROM (select a.vcCityId,
                        a.vcCountryId,
                        a.vcCityName,
                        a.vcInitial,
                        a.vcCityDesc,
                        b.vcCountryName
                  from web2city a, web2country b
                 where a.vcCountryId = b.vcCountryId
                 order by to_number(a.vcCountryId),
                          a.vcInitial)temp

sql-2：
SELECT temp.*, ROWNUM num
          FROM (select a.vcCityId,
                        a.vcCountryId,
                        a.vcCityName,
                        a.vcInitial,
                        a.vcCityDesc,
                        b.vcCountryName
                  from web2city a, web2country b
                 where a.vcCountryId = b.vcCountryId
                 order by to_number(a.vcCountryId),
                          a.vcInitial)temp
         where ROWNUM <= 40

這兩個sql的前40行結果不一樣，為什么呢，其實是因為排序字段的結果不確定的緣故，由于這兩個sql出來的結果有很多都是并列的，所以oracle給出的結果集列表不一定都是固定的。oracle會給出他以最快速度查到的滿足條件的結果，但是卻并不一定滿足你的要求。修改需要加入一個唯一的字段進行排就不會出問題。

sql-3：（正解）
SELECT temp.*, ROWNUM num
          FROM (select a.vcCityId,
                        a.vcCountryId,
                        a.vcCityName,
                        a.vcInitial,
                        a.vcCityDesc,
                        b.vcCountryName
                  from web2city a, web2country b
                 where a.vcCountryId = b.vcCountryId
                 order by to_number(a.vcCountryId),
                          a.vcInitial,
                          to_number(a.vccityid)) temp
         where ROWNUM <= 40

posted on 2007-05-31 11:27 小秦 閱讀(346) 評論(0)  編輯  收藏