MySQL / 6. Сортировка результатов запроса

Сортировка по подстрокам фиксированной длины

Задача
Вы хотите выполнить сортировку, используя части столбца, имеющие фиксированное местоположение.

Решение
Выделите интересующие вас части с помощью функций LEFT(), MID() и RIGHT() и упорядочьте их.

Обсуждение
Предположим, что у вас есть таблица housewares, представляющая собой каталог предметов домашней обстановки, элементы которой снабжены 11-символьными идентификаторами, состоящими их трех частей: трехзначное сокращение для категории изделия (например, DIN для «dining room» – столовая, KIT для «kitchen» – кухня), пятизначный серийный номер и двузначный код страны, где было изготовлено изделие:

mysql> SELECT * FROM housewares;

+----------------+---------------------+
| id                   | description           |
+---------------+---------------------+
| DIN40672US | dining table          |
| KIT00372UK  | garbage disposal |
| KIT01729JP   | microwave oven  |
| BED00038SG | bedside lamp        |
| BTH00485US | shower stall          |
| BTH00415JP  | lavatory               |
+----------------+---------------------+

На самом деле такой способ хранения составных значений идентификаторов не очень удачен, и в дальнейшем мы поговорим о том, как записывать их в отдельные столбцы. Но пока что будем считать, что значения должны храниться именно так.

Если вы хотите упорядочить записи таблицы по значениям id, то просто используйте все значение столбца:

mysql> SELECT * FROM housewares ORDER BY id;

+---------------+---------------------+
| id                   | description          |
+---------------+---------------------+
| BED00038SG | bedside lamp       |
| BTH00415JP  | lavatory              |
| BTH00485US | shower stall         |
| DIN40672US  | dining table         |
| KIT00372UK  | garbage disposal |
| KIT01729JP   | microwave oven  |
+----------------+---------------------+

Но что делать, если потребуется упорядочить записи по одной из трех составляющих, например по стране-изготовителю? Для таких операций удобно применять функции, выделяющие части столбца, такие как LEFT(), MID() и RIGHT(). Эти функции можно использовать для разбиения значений id на три части:mysql> SELECT id,

-> LEFT(id,3) AS category,
-> MID(id,4,5) AS serial,
-> RIGHT(id,2) AS country
-> FROM housewares;

+----------------+-----------+--------+----------+
| id                   | category | serial   | country |
+---------------+------------+--------+----------+
| DIN40672US | DIN          | 40672 | US         |
| KIT00372UK  | KIT          | 00372 | UK         |
| KIT01729JP   | KIT          | 01729 | JP          |
| BED00038SG | BED         | 00038 | SG          |
| BTH00485US | BTH         | 00485 | US          |
| BTH00415JP  | BTH         | 00415 | JP          |
+----------------+-----------+--------+----------+

Любую из этих трех подстрок фиксированной длины можно использовать для сортировки как отдельно, так и в сочетании с другими. Чтобы упорядочить изделия по категории, извлеките составляющую категории и поместите ее в инструкцию ORDER BY:

mysql> SELECT * FROM housewares ORDER BY LEFT(id,3);

+---------------+--------------------+
| id                   | description         |
+---------------+--------------------+
| BED00038SG | bedside lamp      |
| BTH00485US | shower stall        |
| BTH00415JP | lavatory              |
| DIN40672US | dining table         |
| KIT00372UK | garbage disposal |
| KIT01729JP  | microwave oven  |
+---------------+---------------------+

Чтобы выполнить сортировку по серийному номеру изделия, используйте MID() для извлечения из значений id пяти центральных символов начиная с четвертого:

mysql> SELECT * FROM housewares ORDER BY MID(id,4,5);

+----------------+--------------------+
| id                   | description          |
+---------------+---------------------+
| BED00038SG | bedside lamp       |
| KIT00372UK  | garbage disposal |
| BTH00415JP  | lavatory              |
| BTH00485US | shower stall         |
| KIT01729JP   | microwave oven  |
| DIN40672US | dining table          |
+---------------+---------------------+

Похоже на числовую сортировку, но на самом деле она строковая, так как MID() возвращает строки. В данном случае благодаря тому, что у «чисел» имеются начальные нули (обеспечивающие им одинаковую длину), лексический и числовой порядки сортировки совпадают.Для упорядочивания по коду страны используйте два самых правых символа значений id:

mysql> SELECT * FROM housewares ORDER BY RIGHT(id,2);

+---------------+---------------------+
| id                   | description          |
+---------------+---------------------+
| KIT01729JP   | microwave oven |
| BTH00415JP  | lavatory              |
| BED00038SG | bedside lamp       |
| KIT00372UK  | garbage disposal |
| DIN40672US | dining table          |
| BTH00485US | shower stall         |
+----------------+---------------------+

Можно выполнить сортировку и по комбинации подстрок. Например, чтобы отсортировать по коду страны и серийному номеру, создайте такой запрос:

mysql> SELECT * FROM housewares ORDER BY RIGHT(id,2), MID(id,4,5);

+---------------+---------------------+
| id                   | description          |
+---------------+---------------------+
| BTH00415JP  | lavatory              |
| KIT01729JP   | microwave oven |
| BED00038SG | bedside lamp       |
| KIT00372UK  | garbage disposal |
| BTH00485US | shower stall         |
| DIN40672US | dining table          |
+---------------+---------------------+

Статьи по MySQL на эту тему:

Размещение некоторых значений в начале или конце упорядоченного списка
Сортировка IP-адресов в числовом порядке
Сортировка в порядке, определенном пользователем
Сортировка значений ENUM
Сортировка имен хостов по доменам