SQL Server で指定した範囲のデータを取得する

ページングとかで、テーブルから指定した範囲のデータ、例えば21件目から30件目を取りだすとか、そういう時に MySQL なら LIMIT 使って終わりなんですが、SQL Server だと簡単には出来ません。

まぁ、出来ない事はないんですけど、非常に面倒だったので備忘録的な感じで書いておこうと思います。

まず、MySQL の場合の例から。

以下は、TBLテーブルから21件目から30件目を取りだす例です。

SELECT * FROM TBL ORDER BY filed1 LIMIT 20 , 10

簡単です。

LIMIT 開始行, 取得件数

上記の様に指定すれば、範囲を指定して取得できます。
(MySQLの場合は、1件目が 0 なので、20から 10件と言う指定方法で 21件目から30件目と言う感じになります。)

では、SQL Server で同じ事をやるとしたらどうなるのか?

答えから書きますが、以下の様になります。

SELECT * FROM (
SELECT ROW_NUMBER() OVER ( ORDER BY filed1 ) AS row_num, *
FROM TBL
) TMP WHERE row_num BETWEEN 21 AND 30

分かりにくいですよね・・・

このSQLは大きく分けて2つの段階に分ける事が出来ます。

まず、

SELECT ROW_NUMBER() OVER ( ORDER BY filed1 ) AS row_num, *
FROM TBL

の部分。
この部分は、TBLテーブルを filed1 で並べ替えたものに、ROW_NUMBER関数 で1から始まる連続した数値を振っています。

次に、ここで作った row_num を使って between で絞り込むんですが、

SELECT ROW_NUMBER() OVER ( ORDER BY filed1 ) AS row_num, *
FROM TBL
WHERE row_num BETWEEN 21 AND 30

とするとエラーになります。
まぁ、確かに順序を考えれば WHERE には使えませんよね・・・

そこで、この結果を別名の TMP テーブルとして使って、さらに SELECT して範囲を絞り込むというのが

SELECT * FROM (
(さっきの部分)
) TMP WHERE row_num BETWEEN 21 AND 30

の部分です。

まぁ、出来ない事はないですが非常に分かりにくい。
MySQLなら1句追加するだけで終わりなのに、テンポラリのテーブルまで作って実現している感じ・・・


ちなみに、MSDNのサンプルの様にWITH句を使う事も出来ます。

その場合は、以下の様な感じ。

WITH TMP AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY filed1) AS row_num, *
FROM TBL
)
SELECT *
FROM TMP
WHERE row_num BETWEEN 21 AND 30

どちらにしても面倒です。

何にしても一長一短あるという事なんでしょうか?
この部分に関しては圧倒的に MySQL の方が楽です。