ROWNUMよりROW_NUMBER()
ページ送り機能の実装でワークテーブルを使って実現する予定でした。
しかしOracleのROWNUM擬似列を使って実現することになりました。
このROWNUM少々厄介です。
Order byを使うと思ったデータを取得してくれないのです。
たとえば
select id,name from user where rownum <= 10
というSQL文だと10件目までのデータを正しく取ってきてくれます。
しかしこれを
select id,name from user where rownum <= 10 order by name
とすると正しくデータを取ってきてくれません。
ROWNUMの採番がORDER BYする以前に行われることが原因のようです。
この例だと、まず抽出したデータからrownum <= 10のデータをとってきてそれをORDER BYしちゃうみたいです(-_-#)
select id,name,rownum from (select id,name from user order by name) where rownum <= 10
とすると正しく取ってきてくれます。
じゃあ11件目から20件目のデータを取るにはどうすればいいのか？
単純に考えると
select id,name,rownum from (select id,name from user order by name) where rownum >= 11 and rownum <= 20
と書いてやればいいはず。
しかしこのSQL文だとデータを1件も取ってきてくれません．．．(;´ω`)
これを少し改良して
select id,name from (select id,name,rownum rn from user) where rn >= 11 and rn <= 20
とすれば11件目から20件目までのデータを取得してくれます。
やれやれ(^_^;)これで解決と思ったら大きな落とし穴が．．．＿|￣|○
このSQL文にORDER BY句を追加すると正しくデータを取ってきてくれません。
ROWNUMでできるのはこれが限界のようです。
そこでROW_NUMBER()という関数を使用してやるとORDER BYを使用しても正しくデータをとってきてくれます。具体的には
select id,name from (select row_number() over(order by name) rn,id,name from user order by name) where rn >= 11 and rn <= 20
といった感じです。
データ量が多いときのパフォーマンスが少し心配ですがこれは使えます。
