ページング処理のSQLアレコレ

SQL Server T-SQL

昨日のページング処理のSQLを別の書式で書いてみます。

mrgchr.hatenablog.com

Window関数(row_number)を用いたもの

この手の処理ではよく目にするパターンです。
row_numberでソートし行数を付与してBETWEENで欲しい箇所を抜き出します。

SELECT
  todo.*
FROM (
  SELECT
    t.*,
    ROW_NUMBER() OVER (ORDER BY t.[Id]) AS [RowNumber]
  FROM TodoItems as t
  WHERE t.[UserId] = 1
) AS todo
WHERE todo.[RowNumber] BETWEEN 301 AND 320;

CTEを用いたもの

上記をCTE書式で書いたものです。
結果は同じですが、CTEの方が読みやすいという人もいると思います。

;WITH Todo ([Id], [Title], [Done], [UserId], [row]) AS (
  SELECT
    t.[Id], t.[Title], t.[Done], t.[UserId],
    ROW_NUMBER() OVER (ORDER BY t.[Id])
  FROM TodoItems as t
  WHERE t.[UserId] = 1
)
SELECT
 td.Id, td.Title, td.Done, td.UserId
FROM Todo as td
WHERE [row] BETWEEN 301 AND 320;

OFFSETを用いたもの(SQLServer2012以降のみ)

SQLServer2012以降ではOFFSET句が利用可能です。
OFFSETはORDER BYと併用する必要があります。

SELECT
  t.*
FROM TodoItems as t
WHERE t.[UserId] = 1
ORDER BY t.[Id]
OFFSET 300 ROWS FETCH NEXT 20 ROWS ONLY

上記のBETWEENの例と異なるのは、OFFSETの次に指定するのは「先頭から飛ばす数」だという点です。
似たような概念をC#とLINQで表現すると

todos.Where(t => t.UserId == 1).OrderBy(t => t.Id).Skip(300).Take(20)

と言ったところでしょうか。