検索結果を指定件数ずつ抽出表示したい。

Question

データベース（以下、DB）は、SQLite2です。
PHPスクリプトの中で利用しています。

なお、本質問はMySQLとして説明して頂いても結構です。
（恐らく、同じような方法で解決できると思いますので。）

考え方が分かりましたら、あとは自分で調べられますので、
その方向性だけを示して下さるというのでも結構です。

さて、質問に移らせて頂きますが、

DBに検索をかけ、ヒットしたレコードを、
ある特定のカラムをもとにソートし、その中の上から指定件数分抽出する
ということをしたいと考えています。

例をあげます。

テーブル名（tbl_siritori）

id word cate
1 りんご 食べ物
2 ごりら 動物
3 らくだ 動物
4 ダック 動物
5 くり 食べ物
6 リス 動物
7 スイカ 食べ物
8 カメ 動物
9 メロン 食べ物

というDBがあったとして、

このDBに、[cate]に「食」という文字が入っているようなデータを、
[id]順に見て、大きな数字のものを上から３件抽出したい場合、
どのような方法（SQL文）が考えられますか。

３件のデータを表示した後、
さらにそれに続く３件のデータを次ページに表示、
さらにそれに続く３件も同様にさらに次ページに表示…（略）

ということをしようと思っています。

１ページに３件、で、何ページにも渡ってデータを３件ずつ出力していく、ということです。

以上のことを実現可能であるSQL文の中でも、
最も一般的なものを教えて下さい。

$query = 
"SELECT * FROM tbl_siritori WHERE (cate LIKE '%食%') ～～ごにゃごにゃ～～ ORDER BY id DESC";

というような書き方で、１文で解決できるものと思っていますが、
複数の文で絞り込むような形になるのでしょうか。

以上、よろしくお願いいたします。

chukenkenkou · Accepted Answer

LIMIT [x,]y 
あるいは
LIMIT y OFFSET x

検索条件で絞り込まれ、ORDER BYでソートした結果のx+1行目（先頭は0）からy行を得るという指定です。
ORDER BYがないとMySQLは結果を返す順を保証しないので、LIMITを使用する場合は、通常は必ずORDER BYと組み合わせて使用します。
また、検索条件とORDER BYの組み合わせ等でインデクスを有効利用できれば、インデクス上で「x+1番目からy行」を見つけられるので、性能が極端に落ちません。しかし、逆にインデクスでなく母体データをサーチしなければならないような場合は、母体データ件数が多いと、OFFSETで検索対象をスキップするのに大幅に性能劣化するので注意しましょう。

select ～
 from t1
 [where 検索条件]
 ORDER BY ソートの指定
 LIMIT 0,3

とした場合、該当する行の先頭（OFFSET 0）から3行が検索結果として得られます。

select ～
 from t1
 [where 検索条件]
 ORDER BY ソートの指定
 LIMIT 3,3

とした場合、4行目（OFFSET 3）から3行が検索結果として得られます。

さらに、7行目からは LIMIT 6,3 といったように指定します。

SELECT文の構文については、下記参照。
http://dev.mysql.com/doc/refman/5.1/ja/select.html

どういった検索条件やORDER BYの指定だと、インデクスを効率的に使用できるかは、下記参照。ただし、MySQLの話しです。
http://dev.mysql.com/doc/refman/5.1/ja/query-speed.html

chukenkenkou · Answer

#3の文言を、一部訂正します。

＜訂正前＞
母体データ件数が多いと、OFFSETで検索対象をスキップするのに大幅に性能劣化する

＜訂正後＞
母体データ件数が多かったり、検索対象行が多いと、ORDER BYでのソート及びOFFSETで検索対象をスキップするのに大幅に性能劣化する

chukenkenkou · Answer

SELECT文で、LIMIT句、あるいは「LIMIT句とOFFSET句」を組み合わせればいいのでは？

mako_tyo · Answer

私も、同じようなことをやろうとしましたが。。。
見つかりませんでした。

top、not in、unionなど試しました。

Oracleなら簡単な方法があったのですが・・・　orz

私の解決策としては、SQL文で処理するのではなく、プログラムで処理しました。

検索結果を指定件数ずつ抽出表示したい。

LIMIT [x,]y

この回答への補足

#3の文言を、一部訂正します。

この回答への補足

SELECT文で、LIMIT句、あるいは「LIMIT句とOFFSET句」を組み合わせればいいのでは？

この回答への補足

私も、同じようなことをやろうとしましたが。

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング