【MySQL】WHERE IN に指定した順番でレコード取得する方法

例えばあるテーブルからレコードを取得するとき・・・

  • このレコードは絶対に取得結果に含めたい
  • でも他のレコードはランダムに取得させたい

みたいな場面があって、 WHERE IN  で試したけど期待通りに動きません。

でも色々調べてみると、特定レコードを含めるテクニックが見つかりました

ここでは忘備録も兼ね、WHERE IN に指定した順番でレコード取得する方法 を紹介します。

普通に WHERE IN すると昇順で並び替えられてしまう

冒頭の説明だとちょっと分かりにくいので、どういう状況なのか少し説明します。

例えば次みたいなテーブルとデータを作ったとしましょう。

仮にだけど、IDと国コードを管理するみたいなテーブル

 

これを 'AUS', 'USA', 'CHN', 'JPN', 'BRA'  の順番で並び替えたいんです。

それで WHERE IN  を使って、こういうSQLを書いてみました。

これでいけそうだけど、 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() の補数です。

引用元 : MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.5 文字列関数

最初の str  はレコード名、その後に続いてる str1, str2, str3, ..  のインデックスを返すという関数

これを WHERE IN  と組み合わせれば、好きな順番で並び替えができます。

 

実際のSQL例は次の通り

上のSQLのように WHERE IN  に渡したのと同じ順番で、
その次の ORDER BY  句に FIELD( code, 'AUS', 'USA', ...)  を指定するだけです。

これを実行した結果は次の通り

id code
2 AUS
5 USA
3 CHN
1 JPN
4 BRA

 

ちゃんと WHERE IN  に指定した順番で並んでくれてます。

ちなみにレコードをランダム取得するには…

最後に蛇足ですが、レコードをランダム取得する方法 も紹介します。

今紹介した特定のレコードを含めるSQL例と組み合わせれば、

  • 必ず特定レコードを2,3件含める
  • それ以外は全部ランダムで取得

みたいな柔軟なレコード取得ができるようになります。

 

そのやり方はテーブル名を hogehoge  とすると、次みたいな感じ

ポイントは ORDER BY  に RADN()  を渡すことです。

詳しくは次記事で解説したので、興味のある方はご覧ください。

トリッキーな方法だけど、このテクニックを知ってるだけでも色々できて面白いです。

ここまでのまとめ

ということで、簡単にまとめ

  • WHERE IN では並び替えできない
    なぜなら WHERE IN 句は絞り込み条件を指定するためのものだから
  • 解決策は FIELD 関数を使うこと
    クエリの WHERE IN に渡したのと同じ順番で ORDER BY FIELD( xxx, 'C', 'A', 'B' )  みたいに書けば、WHERE IN に指定した順番で並び替え可能

以上、MySQLで WHERE IN に指定した順で並び替えるテクニックでした。

こういう地味なテクニック、これが柔軟なSQLを書くのに意外と役立ちます。

Commentsこの記事についたコメント

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA


このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください