Czasami może się zdarzyć, że w zapytaniu SQL wymagana jest określona kolejność, która nie może być wykonana przy użyciu ASC lub DESC lub przy użyciu specjalnego pola sortowania. MySQL posiada funkcję ORDER BY FIELD, która może być użyta do tego celu.
Przykładowe dane
Przykładowe dane w tym poście używają mojej przykładowej tabeli owoców. Jest to nieco prosta tabela, ale może być użyta do zilustrowania punktu w tym poście całkiem dobrze.
Ordering by specific field values
Tabela owoców ma pole „name” z następującymi unikalnymi wartościami: Apple, Banana, Orange, Pear. Każda z tych unikalnych wartości ma zestaw odmian.
Powiedzmy, dla dobra argumentu, że chcemy uporządkować dane w określonej kolejności według Banana, Jabłka, Gruszki, Pomarańczy, a następnie według ich odmian. Nie da się tego zrobić za pomocą zwykłej klauzuli ORDER BY, ponieważ sortowanie rosnące lub malejące na tym polu nie zadziała. Potrzebowalibyśmy albo jakiejś formy kolumny sortującej, albo innej alternatywy.
Używając funkcji FIELD( ) w klauzuli ORDER BY możemy to osiągnąć. Działa ona poprzez określenie kolumny do posortowania, a następnie wartości do posortowania w kolejności. Na przykład:
SELECT * FROM fruit ORDER BY FIELD(name, 'Banana', 'Apple', 'Pear', 'Orange'), variety;
Dane wynikowe z przykładowej tabeli wyglądają następująco:
+----------+--------+---------------------+| 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 || 8 | Pear | Anjou || 4 | Pear | Bartlett || 2 | Pear | Comice || 5 | Orange | Blood || 3 | Orange | Navel || 9 | Orange | Valencia |+----------+--------+---------------------+
Przypadek
Przy korzystaniu z tej funkcji pojawia się mały „problem”. Wszelkie wartości w kolumnie, które nie są w funkcji FIELD() pojawią się w mniej lub bardziej losowej kolejności przed określonymi wartościami. Na przykład, określając tylko Apple i Banana:
SELECT * FROM fruit ORDER BY FIELD(name, 'Banana', 'Apple') DESC, variety;
Wynika z tego:
+----------+--------+---------------------+| 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 |+----------+--------+---------------------+
Rozwiązanie problemu
Ale normalnie używałbyś tej funkcji tylko wtedy, gdy znane są dokładne kolumny, rozwiązaniem jest odwrócenie kolejności określonych pól i uporządkowanie ich w kolejności malejącej, a następnie wykonanie drugiego sortowania na tym samym polu.
Następujący przykład, pomimo tego jak wygląda, w rzeczywistości sortuje w kolejności Banana, następnie Apple, a następnie inne nazwy w kolejności rosnącej:
SELECT * FROM fruit ORDER BY FIELD(name, 'Apple', 'Banana') DESC, name, variety;
Wynika z tego:
+----------+--------+---------------------+| 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 |+----------+--------+---------------------+
To może być użyteczne rozwiązanie, jeśli określony zestaw wierszy musi pojawić się przed innymi w zbiorze wyników, ale normalnie nie pojawiłby się jako pierwszy przy użyciu kolejności sortowania ASC lub DESC.