例えばあるテーブルからレコードを取得するとき・・・
- このレコードは絶対に取得結果に含めたい
- でも他のレコードはランダムに取得させたい
みたいな場面があって、 WHERE IN で試したけど期待通りに動きません。
でも色々調べてみると、特定レコードを含めるテクニックが見つかりました
ここでは忘備録も兼ね、WHERE IN に指定した順番でレコード取得する方法 を紹介します。
普通に WHERE IN すると昇順で並び替えられてしまう
冒頭の説明だとちょっと分かりにくいので、どういう状況なのか少し説明します。
例えば次みたいなテーブルとデータを作ったとしましょう。
1 2 3 4 5 6 7 | CREATE TABLE countries ( id INT KEY AUTO_INCREMENT, code VARCHAR(255) ); INSERT INTO countries (code) VALUES ('JPN'), ('AUS'), ('CHN'), ('BRA'), ('USA'); |
仮にだけど、IDと国コードを管理するみたいなテーブル
これを 'AUS', 'USA', 'CHN', 'JPN', 'BRA' の順番で並び替えたいんです。
それで WHERE IN を使って、こういうSQLを書いてみました。
1 2 | SELECT * FROM countries WHERE code IN ('AUS', 'USA', 'CHN', 'JPN', 'BRA'); |
これでいけそうだけど、 WHERE IN に並び替える機能はナッシング（当然といえば当然）
こうやると、次みたいな結果が返ってきちゃいます。
| id | code |
|---|---|
| 1 | JPN |
| 2 | AUS |
| 3 | CHN |
| 4 | BRA |
| 5 | USA |
どうやら ID の昇順で並び替えされる模様
つまりレコードを挿入した順番ですね。
こういうレコード並び替えには FIELD 関数を使えばOK
こういう風に WHERE IN だけでは指定した順番では並び替えができません。
ではどうすれば WHERE IN の順番で並び替えできるのか・・・
その解決策は FIELD 関数も合わせて使うことです。
以下は FIELD関数 についての公式リファレンスの説明
FIELD(str,str1,str2,str3,...)
str1、str2、str3、... リスト内で str のインデックス (位置) を返します。str が見つからない場合は、0 を返します。
FIELD() へのすべての引数が文字列の場合は、すべての引数が文字列として比較されます。すべての引数が数値の場合は、数字として比較されます。それ以外の場合は、引数が倍精度として比較されます。
NULL ではどの値との等価比較にも失敗するため、str が NULL である場合は、戻り値が 0 になります。FIELD() は ELT() の補数です。
最初の str はレコード名、その後に続いてる str1, str2, str3, .. のインデックスを返すという関数
これを WHERE IN と組み合わせれば、好きな順番で並び替えができます。
実際のSQL例は次の通り
1 2 3 | SELECT * FROM countries WHERE code IN ('AUS', 'USA', 'CHN', 'JPN', 'BRA') ORDER BY FIELD( code, 'AUS', 'USA', 'CHN', 'JPN', 'BRA'); |
上のSQLのように WHERE IN に渡したのと同じ順番で、
その次の ORDER BY 句に FIELD( code, 'AUS', 'USA', ...) を指定するだけです。
これを実行した結果は次の通り
| id | code |
|---|---|
| 2 | AUS |
| 5 | USA |
| 3 | CHN |
| 1 | JPN |
| 4 | BRA |
ちゃんと WHERE IN に指定した順番で並んでくれてます。
ちなみにレコードをランダム取得するには…
最後に蛇足ですが、レコードをランダム取得する方法 も紹介します。
今紹介した特定のレコードを含めるSQL例と組み合わせれば、
- 必ず特定レコードを２，３件含める
- それ以外は全部ランダムで取得
みたいな柔軟なレコード取得ができるようになります。
そのやり方はテーブル名を hogehoge とすると、次みたいな感じ
1 | SELECT * FROM hogehoge ORDER BY RAND() LIMIT 5 |
ポイントは ORDER BY に RADN() を渡すことです。
詳しくは次記事で解説したので、興味のある方はご覧ください。
トリッキーな方法だけど、このテクニックを知ってるだけでも色々できて面白いです。
ここまでのまとめ
ということで、簡単にまとめ
- WHERE IN では並び替えできないなぜなら WHERE IN 句は絞り込み条件を指定するためのものだから
- 解決策は FIELD 関数を使うことクエリの WHERE IN に渡したのと同じ順番で ORDER BY FIELD( xxx, 'C', 'A', 'B' ) みたいに書けば、WHERE IN に指定した順番で並び替え可能
以上、MySQLで WHERE IN に指定した順で並び替えるテクニックでした。
こういう地味なテクニック、これが柔軟なSQLを書くのに意外と役立ちます。
