oracle排序問題

標籤：欄位   asc   技術分享   rom   style   images   .com   blog   test   

表格test資料

select * from test order by name desc

結果：

 

1-1：select * from ( select A.*,rownum rn from test A order by name asc ) where rn between 0 and 10

結果：

1-2：select * from ( select A.*,rownum rn from test A order by name desc ) where rn between 0 and 10

結果：

2-1：select A.*,rownum from test A where rownum between 0 and 10 order by name desc

結果：

 

2-2：select A.*,rownum from test A where rownum between 0 and 10 order by name asc

結果：

3-1：select * from ( select A.*,Row_Number() OVER (order by name desc) as rn from test A ) where rn between 0 and 10

結果：

3-2：select * from ( select A.*,Row_Number() OVER (order by name asc) as rn from test A ) where rn between 0 and 10

結果：

 

 結論：第三種查詢語句是先排序後取資料，而前面兩種是先取資料後查詢，對於不是主鍵的欄位。

oracle排序問題