昨日のページング処理のSQLを別の書式で書いてみます。
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)
と言ったところでしょうか。