Oracle下用rownum進行分頁時排序的錯亂 .

標籤：style   http   color   strong   資料   ar   問題   html   

今天早上使用者打電話說系統的排序有問題，仔細看了一下，問題好像是出在ROWNUM這個函數上。

在Oracle下ROWNUM函數是對查詢結果按照順序分配自增行序號，所以很多人都利用這個特點進行分頁操作，即rownum between 30 and 60，這種限制返回行數的小竅門在普通情況下都很正常，但如果要對返回結果在進行排序就變得亂套了。

如下查詢：

select page.* from
( select rownum page_id,
    id,
    unit_code,
    name,
    code
FROM lb_sys_twork page
WHERE state=1
ORDER BY unit_code,
    code) Page
where page.page_id between 10
       AND 31

這是一個網上很常用的查詢模式，雖然我在子查詢中加入了ORDER BY unit_code,code，並限制返回記錄集的第10條到30條但是返回集並沒有按照我所想的那樣進行排序，查其原因發現rownum page_id這個偽欄位並不是在最終返回結果集中產生，而是在引用它的子查詢中已經返回，如此情況，在加了order by後返回的結果集也就不是按照1，2，3。。。這樣的順序排列了。我嘗試將rownum page_id放在最外層，如下：

select rownum page_id, page.* from
( select id,
    unit_code,
    name,
    code
FROM lb_sys_twork page
WHERE state=1
ORDER BY unit_code,
    code) Page
where page.page_id between 10
       AND 31

這時查詢出現錯誤，說是找不到Page_id這個欄位，我將page_id的欄位別名去掉，直接用rownum來做欄位，結果集為空白。到這時我才恍然大悟為什麼把rownum叫做偽欄位，rownum是在結果集進行輸出時自動加入的一列，按照這種原理，如果你用Where rownum=10來限制返回的行數，將會得到的是一個空結果集，rownum>10等等也不行。看來把rownum放在子集還是放在最外層都不行，難道沒辦法了嗎？

還是Google幫了忙找到了這片文章，其中介紹了MINUS這個減法集合運算子，MINUS是找到兩個給定的資料集合之間的差異，即找到一個資料集合，該集合的資料是僅存在於前一個資料集而後一個資料集中不存在的，相當於集合1-集合2，利用此函數，我可以讓集合1返回的是前50條資料，而集合2返回的是前30條，50-30就相當於返回的第30-50的資料。查詢SQL如下：

SELECT rownum,
     page.*
FROM ( select Page.*
FROM lb_sys_twork page
WHERE page.state=1
ORDER BY unit_code,
    code) Page
WHERE rownum < 50 MINUS SELECT rownum,
     page.*
FROM ( select Page.*
FROM lb_sys_twork page
WHERE page.state=1
ORDER BY unit_code,
    code) Page
WHERE rownum < 30