SQLite でページング

データベースに SQLite を使っているアプリでページングを実装するために、例えば検索したデータのうち101行目から150行目までを取得したいとき、LIMIT と OFFSET の構文を使ってこう書けます。

SELECT id,
       name,
       price
FROM product
WHERE price BETWEEN 5000 AND 10000
ORDER BY price
LIMIT 50
OFFSET 100

これと同じことを SQL Server でやろうとしたら大変です。ROW_NUMBER() を使って行番号を振り、その行番号を使って絞り込む必要があります。

SELECT id,
       name,
       price
FROM (
      SELECT id,
             name,
             price,
             ROW_NUMBER() OVER(ORDER BY price) as rownum
      FROM product
      WHERE price BETWEEN 5000 AND 10000
     ) as product_
WHERE rownum BETWEEN 101 AND 150
ORDER BY rownum

SQL Server も SQLite みたいに簡単に書けたらいいのにね。

C# や VB で開発しているなら、LINQ 使って簡単に書けます。

var query = context.Product
                   .Where(p => 5000 <= p.Price && p.Price <= 10000)
                   .OrderBy(p => p.Price)
                   .Skip(100)
                   .Take(50)
                   .Select(p => new { p.Id, p.Name, p.Price });

これで、ROW_NUMBER() を使った SQL を発行してくれます。LINQ を使ったコードは、見た目が SQLite のクエリに近いですね。

LIMIT 構文は SQLite で上位 n 件を取得するときにも使ったりします。今後お世話になる事が多いと思うのでメモしておこう。