SQLクエリで特定の順序が必要な場合があり、ASCやDESCを使用したり、特別なソートフィールドを使用して行うことができない場合があります。 MySQL には ORDER BY FIELD 関数があり、これを使用できます。
サンプル データ
この記事のサンプル データは、私の例のフルーツ テーブルを使用しています。
特定のフィールド値による順序付け
果物テーブルには、次の一意の値を持つ「名前」フィールドがあります。 Apple、Banana、Orange、Pearです。 これらの一意の値には、それぞれ品種のセットがあります。
議論のために、データをバナナ、アップル、ペア、オレンジ、そしてそれらの品種で特定の順序で並べたいとします。 このフィールドの昇順または降順の並べ替えは機能しないため、通常の ORDER BY 節を使用してこれを行うことは不可能です。
ORDER BY句でFIELD( )関数を使用すると、これを実現できます。 これは、並べ替えを行う列と、並べ替えを行う値を指定することで動作します。 たとえば、
SELECT * FROM fruit ORDER BY FIELD(name, 'Banana', 'Apple', 'Pear', 'Orange'), variety;
この例のテーブルから得られるデータは、次のようになります。 FIELD()関数にない値が列内にある場合、指定された値の前に多かれ少なかれランダムな順序で表示されます。 たとえば、Apple と Banana のみを指定した場合:
SELECT * FROM fruit ORDER BY FIELD(name, 'Banana', 'Apple') DESC, variety;
この結果:
+----------+--------+---------------------+| fruit_id | name | variety |+----------+--------+---------------------+| 6 | Apple | Cox's Orange Pippin || 7 | Apple | Granny Smith || 1 | Apple | Red Delicious || 11 | Banana | Burro || 12 | Banana | Cavendish || 10 | Banana | Plantain || 8 | Pear | Anjou || 4 | Pear | Bartlett || 5 | Orange | Blood || 2 | Pear | Comice || 3 | Orange | Navel || 9 | Orange | Valencia |+----------+--------+---------------------+
Gotcha の解決策
通常この関数は正確な列がわかっている場合にのみ使用しますが、解決策として指定したフィールドを逆順にして降順に並び、同じフィールドに対して 2 回目のソートを実行することが挙げられます。
次の例は、見た目とは裏腹に、実際には Banana、Apple、そして他の名前の順に昇順でソートします:
SELECT * FROM fruit ORDER BY FIELD(name, 'Apple', 'Banana') DESC, name, variety;
この結果は、
+----------+--------+---------------------+| fruit_id | name | variety |+----------+--------+---------------------+| 11 | Banana | Burro || 12 | Banana | Cavendish || 10 | Banana | Plantain || 6 | Apple | Cox's Orange Pippin || 7 | Apple | Granny Smith || 1 | Apple | Red Delicious || 5 | Orange | Blood || 3 | Orange | Navel || 9 | Orange | Valencia || 8 | Pear | Anjou || 4 | Pear | Bartlett || 2 | Pear | Comice |+----------+--------+---------------------+
結果セット内で特定の行のセットを他の行より先に表示する必要がある場合に役立つソリューションですが、通常 ASC または DESC ソートを使用すると、最初に表示することはありません。