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

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

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

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

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

SELECT * FROM TBL ORDER BY filed1 LIMIT 20, 10

簡単です。

LIMIT 開始行, 取得件数

上記の様に指定すれば、範囲を指定して取得できます。

では、Transact-SQL 的にはどう書くの？

っというのが今回のお話です。

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句を使う事も出来ます。

ROW_NUMBER (Transact-SQL)

learn.microsoft.com

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

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 の方が楽です。