Сортировка в порядке, определенном пользователем

Задача
Вы хотите определить порядок сортировки для всех значений столбца.

Решение
Используйте функцию FIELD() для отображения значений столбцов на последовательность, располагающую значения в нужном порядке.

Обсуждение
В предыдущем разделе было показано, как переместить указанную группу строк в начало упорядоченного списка. Если вы хотите определить специальный порядок для всех значений столбца, примените функцию FIELD(), которая сопоставит их списку числовых значений, и используйте сортировку для чисел. FIELD() сравнивает свой первый аргумент со следующими и возвращает число, показывающее, с которым из них он совпадает. Следующий вызов FIELD() сравнивает аргумент value с аргументами str1, str2, str3 и str4 и возвращает 1, 2, 3 или 4 в зависимости от того, какому из аргументов равно значение value:

FIELD(value,str1,str2,str3,str4)


Значений для сравнения не обязательно должно быть четыре; FIELD() принимает список аргументов переменной длины. Если value – это NULL или совпадения нет, FIELD() возвращает 0.

Функцию FIELD() можно использовать для сортировки произвольного множества значений в любом порядке. Например, чтобы вывести записи табли-цы driver_log для имен Henry, Suzi и Ben именно в таком порядке, выполните запрос:

mysql> SELECT * FROM driver_log
-> ORDER BY FIELD(name,'Henry','Suzi','Ben');


+--------+-------+---------------+------+
| rec_id | name | trav_date | miles |
+--------+-------+---------------+-------+
| 3 | Henry | 2001-11-29 | 300 |
| 4 | Henry | 2001-11-27 | 96 |
| 6 | Henry | 2001-11-26 | 115 |
| 8 | Henry | 2001-12-01 | 197 |
| 10 | Henry | 2001-11-30 | 203 |
| 2 | Suzi | 2001-11-29 | 391 |
| 7 | Suzi | 2001-12-02 | 502 |
| 1 | Ben | 2001-11-30 | 152 |
| 5 | Ben | 2001-11-29 | 131 |
| 9 | Ben | 2001-12-02 | 79 |
+-------+--------+--------------+-------+

FIELD() можно использовать и для подстрок столбцов. Чтобы упорядочить элементы таблицы housewares по стране изготовления в порядке US, UK, JP, SG, сделайте так:

mysql> SELECT id, description FROM housewares
-> ORDER BY FIELD(RIGHT(id,2),'US','UK','JP','SG');


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

Если обобщить, FIELD() может применяться для сортировки любых значений в произвольном порядке, если естественный порядок этих значений не представляет интереса.

Добавлено: 23 Июля 2018 06:11:58 Добавил: Андрей Ковальчук

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

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

Решение
Добавьте в инструкцию ORDER BY еще один столбец сортировки, который поместит эти несколько значений туда, куда нужно. Остальные столбцы сортировки будут оказывать свое обычное воздействие на другие значения.

Обсуждение
Если вы хотите, чтобы результирующее множество было упорядочено обычным образом, за исключением того, что в его начало должны быть помещены определенные значения, создайте дополнительный столбец, который содержит 0 для указанных значений и 1 для остальных. Будем использовать прием, который уже применялся для помещения значений NULL в начало упорядоченного списка (рецепт 6.5). Предположим, что вы хотите отсортировать таблицу сообщений mail по отправителям/получателям, при этом первыми должны выводиться сообщения пользователя phil. Сделаем так:

mysql> SELECT t, srcuser, dstuser, size
-> FROM mail
-> ORDER BY IF(srcuser='phil',0,1), srcuser, dstuser;


+--------------------------+---------+----------+---------+
| t | srcuser | dstuser | size |
+--------------------------+---------+----------+---------+
| 2001-05-16 23:04:19 | phil | barb | 10294 |
| 2001-05-12 15:02:49 | phil | phi | 1048 |
| 2001-05-15 08:50:57 | phil | phil | 978 |
| 2001-05-14 11:52:17 | phil | tricia | 5781 |
| 2001-05-17 12:49:23 | phil | tricia | 873 |
| 2001-05-14 14:42:21 | barb | barb | 98151 |
| 2001-05-11 10:15:08 | barb | tricia | 58274 |
| 2001-05-13 13:59:18 | barb | tricia | 271 |
| 2001-05-14 09:31:37 | gene | barb | 2291 |
| 2001-05-16 09:00:28 | gene | barb | 613 |
| 2001-05-15 07:17:48 | gene | gene | 3824 |
| 2001-05-15 17:35:31 | gene | gene | 3856 |
| 2001-05-19 22:21:51 | gene | gene | 23992 |
| 2001-05-15 10:25:52 | gene | tricia | 998532 |
| 2001-05-12 12:48:13 | tricia | gene | 194925 |
| 2001-05-14 17:03:01 | tricia | phil | 2394482 |
+--------------------------+---------+---------+-----------+

Значение дополнительного столбца сортировки равно 0 для строк, в которых значение srcuser равно phil, и 1 – для остальных строк. Теперь записи для сообщений, отправленных пользователем phil, «всплывут» на самый верх вывода. (Чтобы вместо этого «утопить» их, то есть спустить в самый низ вывода, задайте обратное направление сортировки при помощи DESC или поменяйте местами второй и третий аргументы функции IF().)

Перемещать можно не только конкретные значения, но и записи, для которых выполнены какие-то условия. Чтобы вывести первыми записи для случаев, когда люди писали письма сами себе, выполним такой запрос:

mysql> SELECT t, srcuser, dstuser, size
-> FROM mail
-> ORDER BY IF(srcuser=dstuser,0,1), srcuser, dstuser;


+--------------------------+---------+----------+----------+
| t | srcuser | dstuser | size |
+--------------------------+---------+----------+----------+
| 2001-05-14 14:42:21 | barb | barb | 98151 |
| 2001-05-15 07:17:48 | gene | gene | 3824 |
| 2001-05-15 17:35:31 | gene | gene | 3856 |
| 2001-05-19 22:21:51 | gene | gene | 23992 |
| 2001-05-12 15:02:49 | phil | phil | 1048 |
| 2001-05-15 08:50:57 | phil | phil | 978 |
| 2001-05-11 10:15:08 | barb | tricia | 58274 |
| 2001-05-13 13:59:18 | barb | tricia | 271 |
| 2001-05-14 09:31:37 | gene | barb | 2291 |
| 2001-05-16 09:00:28 | gene | barb | 613 |
| 2001-05-15 10:25:52 | gene | tricia | 998532 |
| 2001-05-16 23:04:19 | phil | barb | 10294 |
| 2001-05-14 11:52:17 | phil | tricia | 5781 |
| 2001-05-17 12:49:23 | phil | tricia | 873 |
| 2001-05-12 12:48:13 | tricia | gene | 194925 |
| 2001-05-14 17:03:01 | tricia | phil | 2394482 |
+--------------------------+----------+----------+----------+

Если вы хорошо представляете себе содержимое вашей таблицы, то, возможно, сумеете обойтись без дополнительного столбца сортировки. Например, в таблице mail столбец srcuser никогда не содержит NULL, поэтому предыдущий запрос можно переписать, используя в инструкции ORDER BY на один столбец меньше (считаем, что значения NULL при сортировке располагаются перед всеми значениями не-NULL):

mysql> SELECT t, srcuser, dstuser, size
-> FROM mail
-> ORDER BY IF(srcuser=dstuser,NULL,srcuser), dstuser;


+--------------------------+---------+---------+---------+
| t | srcuser | dstuser | size |
+--------------------------+---------+---------+---------+
| 2001-05-14 14:42:21 | barb | barb | 98151 |
| 2001-05-15 07:17:48 | gene | gene | 3824 |
| 2001-05-15 17:35:31 | gene | gene | 3856 |
| 2001-05-19 22:21:51 | gene | gene | 23992 |
| 2001-05-12 15:02:49 | phil | phil | 1048 |
| 2001-05-15 08:50:57 | phil | phil | 978 |
| 2001-05-11 10:15:08 | barb | tricia | 58274 |
| 2001-05-13 13:59:18 | barb | tricia | 271 |
| 2001-05-14 09:31:37 | gene | barb | 2291 |
| 2001-05-16 09:00:28 | gene | barb | 613 |
| 2001-05-15 10:25:52 | gene | tricia | 998532 |
| 2001-05-16 23:04:19 | phil | barb | 10294 |
| 2001-05-14 11:52:17 | phil | tricia | 5781 |
| 2001-05-17 12:49:23 | phil | tricia | 873 |
| 2001-05-12 12:48:13 | tricia | gene | 194925 |
| 2001-05-14 17:03:01 | tricia | phil | 2394482 |
+--------------------------+----------+---------+-----------+

Добавлено: 23 Июля 2018 06:11:14 Добавил: Андрей Ковальчук

Сортировка IP-адресов в числовом порядке

Задача
Вы хотите упорядочить строки, представляющие IP-адреса (четверки чисел, разделенные точками) как числа.

Решение
Разбейте строки на составляющие и сортируйте их как числа. Или просто используйте функцию INET_ATON().

Обсуждение
Если таблица содержит IP-адреса, представленные строками в виде четверок чисел, разделенных точками (например, 111.122.133.144), они будут упорядочиваться лексически. Чтобы сформировать вывод, отсортированный в числовом порядке, можно упорядочивать адреса как значения, состоящие из четырех частей, каждая из которых сортируется как число. Применим прием, наподобие используемого для сортировки имен хостов, но имеющий
следующий отличия:

• Четверки чисел, разделенные точками, всегда состоят из четырех сегментов, так что не нужно добавлять точки в начало значения при извлечении подстрок.

• Значения сортируются слева направо, поэтому порядок, в котором подстроки указываются в инструкции ORDER BY, противоположен использованному для сортировки имен хостов.

• Сегменты значений являются числами, поэтому необходимо добавлять к каждой подстроке ноль, чтобы указать MySQL на необходимость выполнения числовой, а не лексической сортировки.

Предположим, что у вас есть таблица hostip, содержащая строковый столбец ip с IP-адресами:

mysql> SELECT ip FROM hostip ORDER BY ip;


+---------------------+
| ip |
+---------------------+
| 127.0.0.1 |
| 192.168.0.10 |
| 192.168.0.2 |
| 192.168.1.10 |
| 192.168.1.2 |
| 21.0.0.1 |
| 255.255.255.255 |
+---------------------+

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

mysql> SELECT ip FROM hostip
-> ORDER BY
-> SUBSTRING_INDEX(ip,'.',1)+0,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-3),'.',1)+0,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-2),'.',1)+0,
-> SUBSTRING_INDEX(ip,'.',-1)+0;


+---------------------+
| ip |
+---------------------+
| 21.0.0.1 |
| 127.0.0.1 |
| 192.168.0.2 |
| 192.168.0.10 |
| 192.168.1.2 |
| 192.168.1.10 |
| 255.255.255.255 |
+---------------------+

Если вы работаете с MySQL версии 3.23.15 или выше, то можете решить задачу проще. Отсортируйте IP-адреса с помощью функции INET_ATON(), которая преобразует сетевой адрес непосредственно в соответствующее число:

mysql> SELECT ip FROM hostip ORDER BY INET_ATON(ip);


+---------------------+
| ip |
+---------------------+
| 21.0.0.1 |
| 127.0.0.1 |
| 192.168.0.2 |
| 192.168.0.10 |
| 192.168.1.2 |
| 192.168.1.10 |
| 255.255.255.255 |
+---------------------+

Если вы надеетесь выполнить сортировку, просто добавив ноль к значению ip и применив ORDER BY к результату, посмотрите, какие значения на самом деле будут получены при преобразовании строк в числа:

mysql> SELECT ip, ip+0 FROM hostip;


+---------------------+-----------+
| ip | ip+0 |
+---------------------+-----------+
| 127.0.0.1 | 127 |
| 192.168.0.2 | 192.168 |
| 192.168.0.10 | 192.168 |
| 192.168.1.2 | 192.168 |
| 192.168.1.10 | 192.168 |
| 255.255.255.255 | 255.255 |
| 21.0.0.1 | 21 |
+---------------------+-----------+

Преобразование оставляет только ту часть каждого значения, которую можно интерпретировать как число. Оставшаяся часть будет недоступна для сортировки, а для вывода корректного результата нужны все составляющие.

Добавлено: 23 Июля 2018 06:10:31 Добавил: Андрей Ковальчук

Сортировка имен хостов по доменам

Задача
Вы хотите упорядочить имена хостов по доменам, при этом правая часть имени является более важной, чем левая.

Решение
Разбейте имена на части и сортируйте части справа налево.

Обсуждение
Имена хостов – это символьные строки, поэтому их естественным порядком является лексический. Однако часто требуется упорядочить имена хостов по доменам, при этом самые правые сегменты значений являются более значимыми, чем самые левые. Предположим, что у вас есть таблица hostname с именами хостов:

mysql> SELECT name FROM hostname ORDER BY name;


+-----------------------+
| name |
+-----------------------+
| cvs.php.net |
| dbi.perl.org |
| jakarta.apache.org |
| lists.mysql.com |
| mysql.com |
| www.kitebird.com |
+-----------------------+

Формирование вывода, отсортированного по домену, – это задача сортировки по подстроке. Необходимо извлечь все сегменты имен, чтобы отсортировать их справа налево. Появляется и дополнительная сложность: значения могут содержать разное количество сегментов, как имена хостов из примера (большинство имен состоит из трех частей, но mysql.com – только из двух).

Чтобы извлечь части имен хостов, для начала используем функцию SUBSTRING_INDEX() так же, как и в рецепте 6.13. Значения имен хостов состоят максимум из трех сегментов, так что слева направо можно извлечь их следующим образом:

SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-3),'.',1)

SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-2),'.',1)

SUBSTRING_INDEX(name,'.',-1)


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

mysql> SELECT name,

-> SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-3),'.',1) AS leftmost,

-> SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-2),'.',1) AS middle,

-> SUBSTRING_INDEX(name,'.',-1) AS rightmost

-> FROM hostname;

+-----------------------+-----------+---------+------------+
| name | leftmost | middle | rightmost |
+-----------------------+----------+----------+------------+
| cvs.php.net | cvs | php | net |
| dbi.perl.org | dbi | perl | org |
| lists.mysql.com | lists | mysql | com |
| mysql.com | mysql | mysql | com |
| jakarta.apache.org | jakarta | apache | org |
| www.kitebird.com | www | kitebird | com |
+-----------------------+-----------+----------+-----------+

Обратите внимание на вывод для строки mysql.com; в столбце leftmost присутствует значение mysql, хотя там должна бы быть пустая строка. Выраженияизвлечения сегментов действуют так: они отбрасывают n сегментов справа и возвращают самый левый сегмент результата. Источник проблемы с mysql.com в том, что если сегментов меньше, чем n, то выражение просто возвращает самый левый сегмент. Чтобы исправить ошибку, добавим в начало имени хоста достаточное количество точек, чтобы гарантировать наличие необходимого количества сегментов:

mysql> SELECT name,

-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('..',name),'.',-3),'.',1)

-> AS leftmost,

-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('.',name),'.',-2),'.',1)

-> AS middle,

-> SUBSTRING_INDEX(name,'.',-1) AS rightmost

-> FROM hostname;


+-----------------------+-----------+----------+-----------+
| name | leftmost | middle | rightmost |
+-----------------------+-----------+----------+-----------+
| cvs.php.net | cvs | php | net |
| dbi.perl.org | dbi | perl | org |
| lists.mysql.com | lists | mysq | com |
| mysql.com | | mysql | com |
| jakarta.apache.org | jakarta | apache | org |
| www.kitebird.com | www | kitebird | com |
+------------------------+----------+----------+-------------+

Выражения выглядят отвратительно, но обеспечивают извлечение подстрок, необходимых для корректной сортировки значений имен хостов справа налево:

mysql> SELECT name FROM hostname

-> ORDER BY

-> SUBSTRING_INDEX(name,'.',-1),

-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('.',name),'.',-2),'.',1),

-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('..',name),'.',-3),'.',1);


+-----------------------+
| name |
+-----------------------+
| www.kitebird.com |
| mysql.com |
| lists.mysql.com |
| cvs.php.net |
| jakarta.apache.org |
| dbi.perl.org |
+-----------------------+

Если максимальное количество сегментов имени хоста равно не трем, а четырем, то необходимо добавить в инструкцию ORDER BY еще одно выражение SUBSTRING_INDEX(), которое помещает в начало значения три точки.

Добавлено: 23 Июля 2018 06:09:30 Добавил: Андрей Ковальчук

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

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

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

Обсуждение
Если подстрока, которую вы хотели бы использовать для сортировки, имеет переменную длину, то необходим надежный способ извлечения именно этой части строки. Чтобы посмотреть, что имеется в виду, создадим таблицу housewares2, похожую на таблицу housewares из предыдущего раздела, но без начальных нулей в серийных номерах, входящих в состав значений id:

mysql> SELECT * FROM housewares2;


+----------------+--------------------+
| id | description |
+---------------+---------------------+
| DIN40672US | dining table |
| KIT372UK | garbage disposal |
| KIT1729JP | microwave oven |
| BED38SG | bedside lamp |
| BTH485US | shower stall |
| BTH415JP | lavatory |
+---------------+---------------------+

Составляющие категории и страны значений id можно извлечь и упорядочить с помощью функций LEFT() и RIGHT(), как и в таблице housewares. Но числовая часть значения теперь имеет переменную длину и уже не может быть извлечена и упорядочена посредством вызова MID(). Применим функцию SUBSTRING() для того, чтобы пропустить три первых символа и вернуть оставшуюся часть значения, начиная с четвертого символа (первой цифры):

mysql> SELECT id, SUBSTRING(id,4) FROM housewares2;


+---------------+---------------------+
| id | SUBSTRING(id,4) |
+---------------+---------------------+
| DIN40672US | 40672US |
| KIT372UK | 372UK |
| KIT1729JP | 1729JP |
| BED38SG | 38SG |
| BTH485US | 485US |
| BTH415JP | 415JP |
+---------------+---------------------+

Затем возьмем все, кроме двух самых правых символов. Сделаем это, например, так:

mysql> SELECT id, LEFT(SUBSTRING(id,4),LENGTH(SUBSTRING(id,4)-2))
-> FROM housewares2;


+---------------+-----------------------------------------------------------------+
| id | LEFT(SUBSTRING(id,4),LENGTH(SUBSTRING(id,4)-2)) |
+---------------+-----------------------------------------------------------------+
| DIN40672US | 40672 |
| KIT372UK | 372 |
| KIT1729JP | 1729 |
| BED38SG | 38 |
| BTH485US | 485 |
| BTH415JP | 415 |
+---------------+------------------------------------------------------------------+

Но можно поступить и проще. Функция SUBSTRING() принимает третий необязательный аргумент, указывающий длину строки, а мы знаем, что длина центральной части значения равна длине строки минус пять (три символа в начале и два символа в конце). Следующий запрос показывает, как получить числовую центральную часть значения, отбрасывая самую правую часть идентификатора изделия:mysql> SELECT id, SUBSTRING(id,4), SUBSTRING(id,4,LENGTH(id)-5) -> FROM housewares2;

+----------------+---------------------+--------------------------------------+
| id | SUBSTRING(id,4) | SUBSTRING(id,4,LENGTH(id)-5) |
+---------------+---------------------+---------------------------------------+
| DIN40672US | 40672US | 40672 |
| KIT372UK | 372UK | 372 |
| KIT1729JP | 1729JP | 1729 |
| BED38SG | 38SG | 38 |
| BTH485US | 485US | 485 |
| BTH415JP | 415JP | 415 |
+--------------+----------------------+---------------------------------------+

К сожалению, несмотря на то что итоговое выражения правильно извлекает числовую часть идентификатора, результирующие значения являются строками. Поэтому они сортируются в лексическом, а не в числовом порядке:

mysql> SELECT * FROM housewares2
-> ORDER BY SUBSTRING(id,4,LENGTH(id)-5);


+---------------+---------------------+
| id | description |
+---------------+---------------------+
| KIT1729JP | microwave oven |
| KIT372UK | garbage disposal |
| BED38SG | bedside lamp |
| DIN40672US | dining table |
| BTH415JP | lavatory |
| BTH485US | shower stall |
+---------------+----------------------+

Что же делать? Можно добавить к ним ноль, чтобы сообщить MySQL о необходимости преобразования строки в число, в результате чего серийные номера будут упорядочены как числа:

mysql> SELECT * FROM housewares2
-> ORDER BY SUBSTRING(id,4,LENGTH(id)-5)+0;


+---------------+---------------------+
| id | description |
+---------------+---------------------+
| BED38SG | bedside lamp |
| KIT372UK | garbage disposal |
| BTH415JP | lavatory |
| BTH485US | shower stall |
| KIT1729JP | microwave oven |
| DIN40672US | dining table |
+---------------+---------------------+

Но в нашем конкретном случае есть более простое решение. Нет необходимости в вычислении длины числовой части строки, так как операция преобразования строки в число отбрасывает замыкающие нечисловые символы и предоставляет значения, которые и требуются для сортировки по серийному номеру – составляющей значений id, имеющей переменную длину. То есть третий аргумент функции SUBSTRING() не нужен:

mysql> SELECT * FROM housewares2

-> ORDER BY SUBSTRING(id,4)+0;


+---------------+---------------------+
| id | description |
+---------------+---------------------+
| BED38SG | bedside lamp |
| KIT372UK | garbage disposal |
| BTH415JP | lavatory |
| BTH485US | shower stall |
| KIT1729JP | microwave oven |
| DIN40672US | dining table |
+---------------+----------------------+

В предыдущем примере для извлечения подстроки переменной длины использовалось то, что в середине значений id стояли символы, отличные от краевых символов (цифры и не-цифры). Возможны случаи, когда значение столбца разбивается на части символами-разделителями. Пусть таблица housewares3 содержит такие значения id:

mysql> SELECT * FROM housewares3;

+------------------+---------------------+
| id | description |
+------------------+---------------------+
| 13-478-92-2 | dining table |
| 873-48-649-63 | garbage disposal |
| 8-4-2-1 | microwave oven |
| 97-681-37-66 | bedside lamp |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
+------------------+---------------------+

Для того чтобы извлекать части таких значений, используйте SUBSTRING_INDEX( str,c,n). Выполняется просмотр строки str в поиске n-го вхождения указанного символа c и возвращается все, что расположено слева от этого символа. Например, следующий вызов возвращает 13-478:

SUBSTRING_INDEX('13-478-92-2','-',2)


Если значение n отрицательное, то просмотр c начинается с правого конца и возвращает все, что расположено слева от найденного символа. Такой вызов вернет 478-92-2:

SUBSTRING_INDEX('13-478-92-2','-',-3)


Используя вызовы SUBSTRING_INDEX() с отрицательными и положительными аргументами, можно последовательно извлекать части из значений id. Можно сначала извлечь первые n сегментов значения, затем отбросить самый правый. Изменяя n от 1 до 4, получим последовательные отрезки значения id, расположенные слева направо:

SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',1),'-',-1)

SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1)

SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',3),'-',-1)

SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',4),'-',-1)


Первое из этих выражений можно оптимизировать, так как внутренний вызов SUBSTRING_INDEX() возвращает односегментную строку, и этого достаточно для получения самой левой составляющей id:

SUBSTRING_INDEX(id,'-',1)


Есть и другой способ получения подстрок – извлекаем n сегментов справа и отбрасываем первый. Будем менять n от –4 до –1:

SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',-4),'-',1)

SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',-3),'-',1)

SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',-2),'-',1)

SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',-1),'-',1)


Опять-таки, возможна оптимизация. В четвертом выражении внутреннего вызова SUBSTRING_INDEX() достаточно для возврата итоговой подстроки:

SUBSTRING_INDEX(id,'-',-1)


Возможно, эти выражения нелегко прочитать и понять, так что, вероятно, вам стоит немного потренироваться, чтобы посмотреть, как они работают.

Рассмотрим пример, показывающий, как получить второй и четвертый сегменты значений id:

mysql> SELECT

-> id,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1) AS segment2,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',4),'-',-1) AS segment4
-> FROM housewares3;


+------------------+------------+------------+
| id | segment2 | segment4 |
+------------------+------------+------------+
| 13-478-92-2 | 478 | 2 |
| 873-48-649-63 | 48 | 63 |
| 8-4-2-1 | 4 | 1 |
| 97-681-37-66 | 681 | 66 |
| 27-48-534-2 | 48 | 2 |
| 5764-56-89-72 | 56 | 72 |
+------------------+------------+------------+

Чтобы использовать подстроки для сортировки, поместите соответствующие выражения в инструкцию ORDER BY. (Не забудьте выполнить преобразование из строки в число, если вас интересует не лексическая, а числовая сортировка.) Приведем два запроса, упорядочивающие результаты по второму сегменту значения id. Первый запрос выполняет лексическую сортировку, а второй – числовую:

mysql> SELECT * FROM housewares3
-> ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1);


+------------------+---------------------+
| id | description |
+------------------+---------------------+
| 8-4-2-1 | microwave oven |
| 13-478-92-2 | dining table |
| 873-48-649-63 | garbage disposal |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
| 97-681-37-66 | bedside lamp |
+------------------+---------------------+

mysql> SELECT * FROM housewares3
-> ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1)+0;


+------------------+---------------------+
| id | description |
+------------------+---------------------+
| 8-4-2-1 | microwave oven |
| 873-48-649-63 | garbage disposal |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
| 13-478-92-2 | dining table |
| 97-681-37-66 | bedside lamp |
+------------------+---------------------+

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

Добавлено: 23 Июля 2018 06:08:38 Добавил: Андрей Ковальчук

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

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

Решение
Выделите интересующие вас части с помощью функций 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 |
+---------------+---------------------+

Добавлено: 23 Июля 2018 06:06:45 Добавил: Андрей Ковальчук

Сортировка по подстрокам значений столбцов

Задача
Вы хотите упорядочить множество значений, используя одну или несколько подстрок каждого значения.

Решение
Извлеките интересующие вас фрагменты и упорядочивайте их по отдельности.

Обсуждение
Для решения задачи используем сортировку по значению выражения. Если вы хотите упорядочить записи, используя только определенную часть значения столбца, извлеките интересующую вас подстроку и используйте ее в инструкции ORDER BY. Проще всего, если подстроки начинаются с фиксированной позиции и имеют фиксированную длину. Подстроки переменной длины или имеющие переменное местоположение можно использовать для сортировки, если имеется надежный способ их идентификации. Несколько последующих рецептов показывают, как применять извлечение подстрок для сортировки в специальном порядке.

Добавлено: 23 Июля 2018 06:05:26 Добавил: Андрей Ковальчук

Сортировка по времени дня

Задача
Вы хотите упорядочить записи по времени дня.

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

Обсуждение
В зависимости от типа столбца сортировку по времени дня можно проводить разными способами. Если значения хранятся в столбце TIME, вы можете непосредственно упорядочивать их. Чтобы расположить по времени дня значения DATETIME или TIMESTAMP, извлеките из них составляющие времени и отсортируйте их. Например, таблица mail содержит значения DATETIME, которые могут быть упорядочены по времени дня следующим образом:

mysql> SELECT * FROM mail ORDER BY HOUR(t), MINUTE(t), SECOND(t);


+--------------------------+---------+---------+----------+---------+----------+
| t | srcuser | srchost | dstuser | dsthost | size |
+--------------------------+---------+---------+----------+---------+----------+
| 2001-05-15 07:17:48 | gene | mars | gene | saturn | 3824 |
| 2001-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2001-05-16 09:00:28 | gene | venus | barb | mars | 613 |
| 2001-05-14 09:31:37 | gene | venus | barb | mars | 2291 |
| 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2001-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2001-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
...

Вы также можете применить функцию TIME_TO_SEC() для отбрасывания составляющей даты и возврата составляющей времени, пересчитанной в секунды:mysql> SELECT * FROM mail ORDER BY TIME_TO_SEC(t);

+--------------------------+---------+---------+----------+---------+----------+
| t | srcuser | srchost | dstuser | dsthost | size |
+--------------------------+---------+---------+----------+---------+----------+
| 2001-05-15 07:17:48 | gene | mars | gene | saturn | 3824 |
| 2001-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2001-05-16 09:00:28 | gene | venus | barb | mars | 613 |
| 2001-05-14 09:31:37 | gene | venus | barb | mars | 2291 |
| 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2001-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2001-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
...

Добавлено: 22 Июля 2018 21:46:40 Добавил: Андрей Ковальчук

Сортировка по дню недели

Задача
Вы хотите выполнить сортировку по дню недели.

Решение
Используйте функцию DAYOFWEEK() для преобразования столбца даты в соответствующее числовое значение дня недели.

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

Вы можете получить день недели, применяя DAYNAME(), но тогда на выходе будут строки, упорядоченные в лексическом порядке, а не как дни недели(воскресенье, понедельник, вторник и т. д.). Тут нам пригодится методика вывода одного значения, а упорядочивания по другому. Будем выводить названия дней при помощи функции DAYNAME(), а сортировать их в порядке дней недели будем посредством функции DAYOFWEEK(), которая возвращает числовые значения от 1 до 7 для дней с воскресенья по субботу:

mysql> SELECT DAYNAME(date) AS day, date, description
-> FROM event
-> ORDER BY DAYOFWEEK(date);


+----------+------------+-------------------------------------+
| day | date | description |
+------------+--------------+----------------------------------------+
| Sunday | 1776-07-14 | Bastille Day |
| Sunday | 1809-02-12 | Abraham Lincoln's birthday |
| Monday | 1215-06-15 | Signing of the Magna Carta |
| Tuesday | 1944-06-06 | D-Day at Normandy Beaches |
| Thursday | 1989-11-09 | Opening of the Berlin Wall |
| Friday | 1957-10-04 | Sputnik launch date |
| Friday | 1958-01-31 | Explorer 1 launch date |
| Friday | 1732-02-22 | George Washington's birthday |
| Saturday | 1789-07-04 | US Independence Day |
| Saturday | 1919-06-28 | Signing of the Treaty of Versailles |
+-----------+---------------+----------------------------------------+

Если вы хотите выполнить сортировку по дню недели, но так, чтобы первым днем недели считался понедельник, используйте функцию MOD() для сопоставления понедельнику значения 0, вторнику – 1, ..., воскресенью – 6:

mysql> SELECT DAYNAME(date), date, description
-> FROM event
-> ORDER BY MOD(DAYOFWEEK(date) + 5, 7);


+--------------------+--------------+----------------------------------------+
| DAYNAME(date) | date | description |
+-------------------+---------------+-----------------------------------------+
| Monday | 1215-06-15 | Signing of the Magna Carta |
| Tuesday | 1944-06-06 | D-Day at Normandy Beaches |
| Thursday | 1989-11-09 | Opening of the Berlin Wall |
| Friday | 1957-10-04 | Sputnik launch date |
| Friday | 1958-01-31 | Explorer 1 launch date |
| Friday | 1732-02-22 | George Washington's birthday |
| Saturday | 1789-07-04 | US Independence Day |
| Saturday | 1919-06-28 | Signing of the Treaty of Versailles |
| Sunday | 1776-07-14 | Bastille Day |
| Sunday | 1809-02-12 | Abraham Lincoln's birthday |
+--------------------+--------------+-----------------------------------------+

Любой день недели можно сделать первым в упорядоченном списке.

Для сортировки по дню недели можно использовать и функцию WEEKDAY(), помня, что она возвращает другой набор значений (0 для понедельника, …, 6 – для воскресенья).

Добавлено: 22 Июля 2018 21:46:09 Добавил: Андрей Ковальчук

Сортировка по календарному дню

Задача
Вы хотите выполнить сортировку по дню календарного года.

Решение
Используйте для сортировки месяц и день, игнорируя годовую составляющую даты.

Обсуждение

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

mysql> SELECT date, description FROM event ORDER BY date;


+--------------+----------------------------------------+
| date | description |
+--------------+----------------------------------------+
| 1215-06-15 | Signing of the Magna Carta |
| 1732-02-22 | George Washington's birthday |
| 1776-07-14 | Bastille Day |
| 1789-07-04 | US Independence Day |
| 1809-02-12 | Abraham Lincoln's birthday |
| 1919-06-28 | Signing of the Treaty of Versailles |
| 1944-06-06 | D-Day at Normandy Beaches |
| 1957-10-04 | Sputnik launch date |
| 1958-01-31 | Explorer 1 launch date |
| 1989-11-09 | Opening of the Berlin Wall |
+--------------+----------------------------------------+

Чтобы расположить их в календарном порядке, отсортируйте их по месяцу, затем по дню месяца:

mysql> SELECT date, description FROM event
-> ORDER BY MONTH(date), DAYOFMONTH(date);


+------------+-------------------------------------+
| date | description |
+--------------+----------------------------------------+
| 1958-01-31 | Explorer 1 launch date |
| 1809-02-12 | Abraham Lincoln's birthday |
| 1732-02-22 | George Washington's birthday |
| 1944-06-06 | D-Day at Normandy Beaches |
| 1215-06-15 | Signing of the Magna Carta |
| 1919-06-28 | Signing of the Treaty of Versailles |
| 1789-07-04 | US Independence Day |
| 1776-07-14 | Bastille Day |
| 1957-10-04 | Sputnik launch date |
| 1989-11-09 | Opening of the Berlin Wall |
+--------------+-----------------------------------------+

MySQL включает в себя функцию DAYOFYEAR(), которая может быть полезна для упорядочивания по календарному дню:mysql> SELECT date, description FROM event ORDER BY DAYOFYEAR(date);

+--------------+----------------------------------------+
| date | description |
+--------------+----------------------------------------+
| 1958-01-31 | Explorer 1 launch date |
| 1809-02-12 | Abraham Lincoln's birthday |
| 1732-02-22 | George Washington's birthday |
| 1944-06-06 | D-Day at Normandy Beaches |
| 1215-06-15 | Signing of the Magna Carta |
| 1919-06-28 | Signing of the Treaty of Versailles |
| 1789-07-04 | US Independence Day |
| 1776-07-14 | Bastille Day |
| 1957-10-04 | Sputnik launch date |
| 1989-11-09 | Opening of the Berlin Wall |
+--------------+----------------------------------------+

Кажется, что все хорошо, но это лишь потому, что в таблице нет записей,
представляющих трудности для обработки посредством DAYOFYEAR(): функ-
ция может выдавать одно значение для разных календарных дней. Напри-
мер, 29 февраля високосного года и 1 марта невисокосного года будут счи-
таться одним и тем же днем:

mysql> SELECT DAYOFYEAR('1996-02-29'), DAYOFYEAR('1997-03-01');


+--------------------------------+---------------------------------+
| DAYOFYEAR('1996-02-29') | DAYOFYEAR('1997-03-01') |
+--------------------------------+---------------------------------+
| 60 | 60 |
+--------------------------------+---------------------------------+

Такая особенность функции DAYOFYEAR() означает, что результаты календарной сортировки, выполненной с ее помощью, не всегда будут корректными. Она может сгруппировать вместе даты, на самом деле являющиеся разными календарными днями.

Если даты в таблице представлены в отдельных столбцах для года, месяца и дня, то для календарной сортировки не потребуется извлекать составляющие. Просто извлекайте непосредственно интересующие вас столбцы. Например, в таблице мастеров бейсбола базы данных baseball1.com имена и даты рождения приводятся так:

mysql> SELECT lastname, firstname, birthyear, birthmonth, birthday
-> FROM master;


+------------------+-----------+------------+-------------+-----------+
| lastname | firstname | birthyear | birthmonth | birthday |
+-----------------+------------+------------+-------------+-----------+
| AARON | HANK | 1934 | 2 | 5 |
| AARON | TOMMIE | 1939 | 8 | 5 |
| AASE | DON | 1954 | 9 | 8 |
| ABAD | ANDY | 1972 | 8 | 25 |
| ABADIE | JOHN | 1854 | 11 | 4 |
| ABBATICCHIO | ED | 1877 | 4 | 15 |
| ABBEY | BERT | 1869 | 11 | 29 |
| ABBEY | CHARLIE | 1866 | 10 | 14 |

Чтобы упорядочить записи в календарном порядке, используем столбцы birthmonth и birthday. Если вы не хотите, чтобы в рамках одного дня записи остались неупорядоченными, то можете добавить дополнительные столбцы сортировки. Следующий запрос выбирает игроков, чьи дни рожденья нам известны, упорядочивает их в календарном порядке и сортирует по имени для каждого календарного дня:

mysql> SELECT lastname, firstname, birthyear, birthmonth, birthday
-> FROM master
-> WHERE birthmonth IS NOT NULL AND birthday IS NOT NULL
-> ORDER BY birthmonth, birthday, lastname, firstname;


+------------------+-----------+------------+-------------+-----------+
| lastname | firstname | birthyear | birthmonth | birthday |
+-----------------+------------+------------+-------------+-----------+
| ALLEN | ETHAN | 1904 | 1 | 1 |
| BEIRNE | KEVIN | 1974 | 1 | 1 |
| BELL | RUDY | 1881 | 1 | 1 |
| BERTHRONG | HARRY | 1844 | 1 | 1 |
| BETHEA | BILL | 1942 | 1 | 1 |
| BISHOP | CHARLIE | 1924 | 1 | 1 |
| BOBB | RANDY | 1948 | 1 | 1 |
| BRUCKMILLER | ANDY | 1882 | 1 | 1 |
...
Для больших наборов данных сортировка по отдельным столбцам составляющих даты может оказаться гораздо быстрее сортировки, извлекающей эти составляющие из значений DATE. Не требуется затрат на извлечение части значения, кроме того, что еще важнее, появляется возможность создания отдельных индексов для составляющих даты, что невозможно при работе со значениями типа DATE.

Добавлено: 22 Июля 2018 21:45:32 Добавил: Андрей Ковальчук

Сортировка по дате

Задача
Вы хотите произвести упорядочивание по временному значению.

Решение
Сортируйте по столбцу даты или времени, при необходимости игнорируя ненужные составляющие значений.

Обсуждение
Многие сведения включают в себя информацию о дате или времени, и нередко требуется упорядочить результаты в хронологическом порядке. MySQL умеет упорядочивать временные типы, так что не приходится прибегать к каким-то ухищрениям, чтобы отсортировать значения столбцов DATE, DATETIME, TIME или TIMESTAMP. Пусть некоторая таблица содержит столбцы всех этих типов:

mysql> SELECT * FROM temporal_val;


+--------------+--------------------------+------------+---------------------+
| d | dt | t | ts |
+--------------+--------------------------+------------+---------------------+
| 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 19800101020000 |
| 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 20210101030000 |
| 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 19750101040000 |
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 19850101050000 |
+--------------+--------------------------+------------+---------------------+

Используем инструкцию ORDER BY для упорядочивания каждого из столбцов в нужном направлении:

mysql> SELECT * FROM temporal_val ORDER BY d;


+--------------+---------------------------+-----------+---------------------+
| d | dt | t | ts |
+--------------+--------------------------+------------+---------------------+
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 19850101050000 |
| 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 19800101020000 |
| 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 19750101040000 |
| 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 20210101030000 |
+--------------+--------------------------+------------+---------------------+

mysql> SELECT * FROM temporal_val ORDER BY dt;


+--------------+--------------------------+------------+---------------------+
| d | dt | t | ts |
+--------------+--------------------------+------------+---------------------+
| 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 20210101030000 |
| 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 19750101040000 |
| 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 19800101020000 |
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 19850101050000 |
+--------------+--------------------------+------------+---------------------+

mysql> SELECT * FROM temporal_val ORDER BY t;


+--------------+--------------------------+------------+---------------------+
| d | dt | t | ts |
+--------------+--------------------------+------------+---------------------+
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 19850101050000 |
| 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 19750101040000 |
| 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 19800101020000 |
| 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 20210101030000 |
+--------------+--------------------------+------------+---------------------+

mysql> SELECT * FROM temporal_val ORDER BY ts;


+--------------+--------------------------+------------+---------------------+
| d | dt | t | ts |
+--------------+--------------------------+------------+---------------------+
| 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 19750101040000 |
| 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 19800101020000 |
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 19850101050000 |
| 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 20210101030000 |
+--------------+--------------------------+------------+---------------------+

Иногда сортировка использует только часть даты или времени. В этом случае можно выделить необходимую составляющую (или составляющие) и работать только с ними.

Добавлено: 22 Июля 2018 21:44:34 Добавил: Андрей Ковальчук

Сортировка и чувствительность к регистру

Задача
Сортировка строк чувствительна к регистру тогда, когда вы этого не хотите, и наоборот.

Решение
Измените чувствительность к регистру упорядочиваемых значений.

Обсуждение
В главе 4 рассказывалось о том, что двоичные строки чувствительны к регистру, а недвоичные – не чувствительны. Это свойство переносится и на операцию сортировки строк: ORDER BY выполняет сортировку в лексическом порядке, при этом для двоичных строк операция чувствительна к регистру, а для недвоичных – не чувствительна. Рассмотрим для наглядности таблицу textblob_ val, которая содержит столбец tstr типа TEXT и столбец bstr типа BLOB:

mysql> SELECT * FROM textblob_val;


+-----+------+
| tstr | bstr |
+-----+------+
| aaa | aaa |
| AAA | AAA |
| bbb | bbb |
| BBB | BBB |
+-----+------+

Столбцы содержат одинаковые значения, но упорядочены будут по-разному, поскольку столбцы TEXT не чувствительны к регистру, а столбцы BLOB – чувствительны:

mysql> SELECT tstr FROM textblob_val ORDER BY tstr;


+-----+
| tstr |
+-----+
| aaa |
| AAA |
| bbb |
| BBB |
+-----+

mysql> SELECT bstr FROM textblob_val ORDER BY bstr;


+-----+
| bstr |
+-----+
| AAA |
| BBB |
| aaa |
| bbb |
+-----+

Чтобы регулировать чувствительность к регистру инструкций ORDER BY, используем приемы, представленные в главе 4 при обсуждении аналогичного воздействия на операции сравнения строк. Чтобы выполнить чувствительное к регистру упорядочивание нечувствительных к регистру строк (в данном случае значений столбца tstr), приведите тип столбца к двоичным строкам, используя ключевое слово BINARY:

mysql> SELECT tstr FROM textblob_val ORDER BY BINARY tstr;


+-----+
| tstr |
+-----+
| AAA |
| BBB |
| aaa |
| bbb |
+-----+

Можно также преобразовать столбец вывода в двоичный и отсортировать его:

mysql> SELECT BINARY tstr FROM textblob_val ORDER BY 1;


+--------------+
| BINARY tstr |
+--------------+
| AAA |
| BBB |
| aaa |
| bbb |
+--------------+

Начиная с версии MySQL 4.0.2 можно применять специальную функцию приведения типов CAST():

mysql> SELECT tstr FROM textblob_val ORDER BY CAST(tstr AS BINARY);


+-----+
| tstr |
+-----+
| AAA |
| BBB |
| aaa |
| bbb |
+-----+

Можно сделать и все наоборот: сортировать двоичные строки без учета регистра. Для этого преобразуйте все значения к верхнему или нижнему регистру с помощью функций UPPER() или LOWER():

mysql> SELECT bstr FROM textblob_val ORDER BY UPPER(bstr);


+-----+
| bstr |
+-----+
| aaa |
| AAA |
| bbb |
| BBB |
+-----+

Или можно преобразовать столбец вывода и упорядочивать его, но это может привести к нежелательным изменениям в представлении выводимых значений:

mysql> SELECT UPPER(bstr) FROM textblob_val ORDER BY 1;


+---------------+
| UPPER(bstr) |
+---------------+
| AAA |
| AAA |
| BBB |
| BBB |
+---------------+

Добавлено: 22 Июля 2018 21:43:43 Добавил: Андрей Ковальчук

Сортировка и значения NULL

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

Решение
Расположение значений NULL в упорядоченном списке менялось с течением времени и зависит от используемой вами версии MySQL. Если значения NULL появляются не там, где хотелось бы, измените их местоположение принудительно.

Обсуждение
Если упорядоченный столбец содержит значения NULL, MySQL группирует их и помещает все вместе. Учитывая то, что в операциях сравнения (как видно из следующего запроса) значения NULL считаются не равными друг другу, такое поведение MySQL может показаться странным:

mysql> SELECT NULL = NULL;


+----------------+
| NULL = NULL |
+----------------+
| NULL |
+----------------+

С другой стороны, значения NULL все же больше похожи друг на друга, чем на значения не-NULL, и, в любом случае, хорошего способа различать значения NULL пока не предложено. Итак, значения NULL группируются вместе и могут помещаться или в начало, или в конец отсортированного списка, в зависимости от используемой версии MySQL. До версии 4.0.2 значения NULL располагались в начале списка (или в конце, при сортировке по убыванию). Начиная с версии 4.0.2, MySQL упорядочивает значения NULL согласно спецификации ANSI SQL и всегда помещает их в начало списка, вне зависимости от направления сортировки.Если вы хотите поместить значения NULL в тот или другой конец упорядоченого вывода, то можете сделать это вне зависимости от того, с какой версией MySQL работаете. Пусть есть таблица t, имеющая такое содержимое:

mysql> SELECT val FROM t;


+-------+
| val |
+-------+
| 3 |
| 100 |
| NULL |
| NULL |
| 9 |
+-------+

Обычно сортировка располагает значения NULL в начале списка:

mysql> SELECT val FROM t ORDER BY val;


+------+
| val |
+------+
| NULL |
| NULL |
| 3 |
| 9 |
| 100 |
+------+

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

mysql> SELECT val FROM t ORDER BY IF(val IS NULL,1,0), val;


+------+
| val |
+------+
| 3 |
| 9 |
| 100 |
| NULL |
| NULL |
+------+

То же самое возможно и для DESC:

mysql> SELECT val FROM t ORDER BY IF(val IS NULL,1,0), val DESC;


+------+
| val |
+------+
| 100 |
| 9 |
| 3 |
| NULL |
| NULL |
+------+

Если оказывается, что MySQL помещает значения NULL в конец упорядоченного списка, а вам бы хотелось, чтобы они были в начале, используйте тот же прием, только поменяйте местами второй и третий аргументы функции IF(), чтобы сопоставлять значениям NULL значение меньшее, чем значения не-NULL:

IF(val IS NULL,0,1)

Добавлено: 22 Июля 2018 21:42:55 Добавил: Андрей Ковальчук

Сортировка одного набора значений и вывод другого

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

Решение
Никаких проблем. Вы можете использовать в инструкции ORDER BY столбцы, которые не указаны в списке вывода запроса.

Обсуждение
Инструкция ORDER BY может упорядочивать не только столбцы, перечисленные в списке вывода, но и «скрытые» (невыводимые) значения. Такой прием, как правило, используется, когда у вас есть значения, которые могут быть представлены несколькими способами, а вы хотите отображать один тип значений, а сортировать по другому. Например, вы можете захотеть вывести размеры почтовых сообщений не как количество байт (числа), а как
строки, то есть 103K для 103 Кбайт. Для преобразования количества байтов в такую строку используйте следующее выражение:

CONCAT(FLOOR((size+1023)/1024),'K')


Получившиеся значения – это строки, поэтому они упорядочиваются в лексическом, а не в числовом порядке. Если вы выполните для них сортировку, то значение 96K будет стоять после 2339K, несмотря на то, что представляет меньший размер:

mysql> SELECT t, srcuser,
-> CONCAT(FLOOR((size+1023)/1024),'K') AS size_in_K
-> FROM mail WHERE size > 50000
-> ORDER BY size_in_K;


+--------------------------+---------+------------+
| t | srcuser | size_in_K |
+--------------------------+---------+------------+
| 2001-05-12 12:48:13 | tricia | 191K |
| 2001-05-14 17:03:01 | tricia | 2339K |
| 2001-05-11 10:15:08 | barb | 57K |
| 2001-05-14 14:42:21 | barb | 96K |
| 2001-05-15 10:25:52 | gene | 976K |
+--------------------------+---------+-----------+

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

mysql> SELECT t, srcuser,
-> CONCAT(FLOOR((size+1023)/1024),'K') AS size_in_K
-> FROM mail WHERE size > 50000
-> ORDER BY size;


+--------------------------+---------+------------+
| t | srcuser | size_in_K |
+--------------------------+---------+------------+
| 2001-05-11 10:15:08 | barb | 57K |
| 2001-05-14 14:42:21 | barb | 96K |
| 2001-05-12 12:48:13 | tricia | 191K |
| 2001-05-15 10:25:52 | gene | 976K |
| 2001-05-14 17:03:01 | tricia | 2339K |
+--------------------------+---------+------------+

Вывод в виде строк значений, отсортированных как числа, может помочь в ряде затруднительных ситуаций. Членам спортивных команд обычно присваивают номер, который присутствует у них на форме. Первое, что приходит в голову, – хранить его в числовом столбце. Но не торопитесь! Некоторым нравится номер ноль (0), а некоторым – двойной ноль (00). Если два таких игрока встретятся в одной команде, вы не сможете хранить их номера в числовом столбце, поскольку значения будут трактоваться как одинаковые.

Поэтому следует хранить номера как строки:

CREATE TABLE roster
(
name CHAR(30), # имя игрока
jersey_num CHAR(3) # номер на футболке
);


Тогда номера будут отображаться так, как вводятся, при этом 0 и 00 будут восприниматься как разные значения. К сожалению, хотя представление чисел в виде строк и решает проблему распознавания 0 и 00, оно вызывает проблемы другого рода. Пусть в команде есть такие игроки:

mysql> SELECT name, jersey_num FROM roster;


+-----------+---------------+
| name | jersey_num |
+-----------+--------------+
| Lynne | 29 |
| Ella | 0 |
| Elizabeth | 100 |
| Nancy | 00 |
| Jean | 8 |
| Sherry | 47 |
+-----------+--------------+

Проблема возникает, когда вы пытаетесь упорядочить членов команды по номеру. Если номера хранятся как строки, они будут отсортированы в лексическом порядке, который часто отличается от числового. Для игроков нашей команды это так и есть:mysql> SELECT name, jersey_num FROM roster ORDER BY jersey_num;

+-----------+------------+
| name | jersey_num |
+-----------+---------------+
| Ella | 0 |
| Nancy | 00 |
| Elizabeth | 100 |
| Lynne | 29 |
| Sherry | 47 |
| Jean | 8 |
+-----------+---------------+

Значения 100 и 8 стоят явно не на своих местах. Но здесь нет ничего сложного. Выводите строковые значения, но для сортировки используйте числа. Сложите значения jersey_num с нулем, чтобы вызвать преобразование строк в числа:

mysql> SELECT name, jersey_num FROM roster ORDER BY jersey_num+0;


+-----------+---------------+
| name | jersey_num |
+-----------+---------------+
| Ella | 0 |
| Nancy | 00 |
| Jean | 8 |
| Lynne | 29 |
| Sherry | 47 |
| Elizabeth | 100 |
+-----------+---------------+

Методику вывода одного значения и сортировки по другому также удобно использовать при выводе композитных значений, составленных из нескольких столбцов, которые упорядочиваются не так, как хотелось бы. Например, таблица mail представляет данные об отправителях сообщений в двух отдельных столбцах: srcuser и srchost. Если вы хотите вывести для отправителей адреса в формате srcuser@srchost, то можете получить такие значения, используя выражение:

CONCAT(srcuser,'@',srchost)


Но эти значения не удобны для сортировки, если название хоста для вас важнее, чем имя пользователя (которое стоит первым). Будем упорядочивать не по составному значению, а по значению базового столбца:

mysql> SELECT t, CONCAT(srcuser,'@',srchost) AS sender, size
-> FROM mail WHERE size > 50000
-> ORDER BY srchost, srcuser;


+---------------------------+----------------+-----------+
| t | sender | size |
+--------------------------+-----------------+-----------+
| 2001-05-15 10:25:52 | gene@mars | 998532 |
| 2001-05-12 12:48:13 | tricia@mars | 194925 |
| 2001-05-11 10:15:08 | barb@saturn | 58274 |
| 2001-05-14 17:03:01 | tricia@saturn | 2394482 |
| 2001-05-14 14:42:21 | barb@venus | 98151 |
+--------------------------+----------------+------------+

То же самое часто проделывают с именами людей. Пусть есть таблица names, содержащая имена и фамилии. Если значения столбцов выводятся по отдельности, то выполнить сортировку сначала по фамилии, а затем по имени просто:

mysql> SELECT last_name, first_name FROM name
-> ORDER BY last_name, first_name;


+-------------+-------------+
| last_name | first_name |
+-------------+------------+
| Blue | Vida |
| Brown | Kevin |
| Gray | Pete |
| White | Devon |
| White | Rondel |
+-------------+------------+

Если же вы хотите выводить для каждого человека строку в виде «имя-пробел-фамилия», то можете начать запрос так:

SELECT CONCAT(first_name,' ',last_name) AS full_name FROM name ...


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

mysql> SELECT CONCAT(first_name,' ',last_name) AS full_name
-> FROM name
-> ORDER BY last_name, first_name;


+-----------------+
| full_name |
+-----------------+
| Vida Blue |
| Kevin Brown |
| Pete Gray |
| Devon White |
| Rondell White |
+-----------------+

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

Дело в том, что выражения в инструкции ORDER BY разрешены только начиная с версии MySQL 3.23.2.

Для того чтобы обойти это ограничение, покажем скрытый элемент – выражение, добавив его в список столбцов вывода, и будем ссылаться на него по номеру позиции столбца или при помощи псевдонима. Например, чтобы написать запрос, выводящий имена из таблицы names, начиная с самого длинного, в MySQL версии 3.23.2 и выше поступим так:

mysql> SELECT CONCAT(first_name,' ',last_name) AS name
-> FROM names-> ORDER BY LENGTH(CONCAT(first_name,' ',last_name)) DESC;


+-----------------+
| name |
+-----------------+
| Rondell White |
| Kevin Brown |
| Devon White |
| Vida Blue |
| Pete Gray |
+----------------+

Чтобы переписать запрос для более ранних версий MySQL, поместим выражение в список столбцов вывода и используем для него псевдоним:

mysql> SELECT CONCAT(first_name,' ',last_name) AS name,
-> LENGTH(CONCAT(first_name,' ',last_name)) AS len
-> FROM names
-> ORDER BY len DESC;


+-----------------+----+
| name | len |
+-----------------+----+
| Rondell White | 13 |
| Kevin Brown | 11 |
| Devon White | 11 |
| Vida Blue | 9 |
| Pete Gray | 9 |
+----------------+----+

Или сошлемся на дополнительный столбец вывода по номеру позиции:

mysql> SELECT CONCAT(first_name,' ',last_name) AS name,
-> LENGTH(CONCAT(first_name,' ',last_name)) AS len
-> FROM names
-> ORDER BY 2 DESC;


+---------------+------+
| name | len |
+---------------+------+
| Rondell White | 13 |
| Kevin Brown | 11 |
| Devon White | 11 |
| Vida Blue | 9 |
| Pete Gray | 9 |
+---------------+------+

Какой бы способ ссылки вы ни выбрали, в выводе будет присутствовать столбец, который нужен там только для обеспечения возможности сортировки, а на самом деле вы абсолютно не заинтересованы в его выводе. Если вы выполняете запрос в программе mysql, то, к сожалению, ничего не можете сделать с этим дополнительным столбцом вывода. В ваших же собственных программах этот столбец не создает проблем. Да, он будет возвращен в результирующем множестве, но вы можете его игнорировать. Проиллюстрируем вышесказанное примером программы на Python. Выполняется запрос, имена отображаются, а длины имен отбрасываются:

cursor = conn.cursor (MySQLdb.cursors.DictCursor)
cursor.execute ("""
SELECT CONCAT(first_name,' ',last_name) AS full_name,
LENGTH(CONCAT(first_name,' ',last_name)) AS len
FROM name
ORDER BY len DESC
""")
for row in cursor.fetchall ():
print row["full_name"] # вывести имя, игнорировать длину
cursor.close ()

Добавлено: 22 Июля 2018 21:42:02 Добавил: Андрей Ковальчук

Сортировка результатов выражения

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

Решение
Поместите выражение, вычисляющее значение, в инструкцию ORDER BY. Если вы работаете со старой версией MySQL, не поддерживающей выражения в ORDER BY, используйте обходной маневр.

Обсуждение
Один из столбцов таблицы mail содержит величину сообщения в байтах:

mysql> SELECT * FROM mail;


+--------------------------+---------+---------+----------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size
+--------------------------+---------+---------+----------+---------+---------+
| 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2001-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
| 2001-05-13 13:59:18 | barb | saturn | tricia | venus | 271 |

... Предположим, что вы хотите извлекать только «большие» сообщения (например, превышающие 50 000 байт), но при этом выводить и упорядочивать их в терминах килобайтов, а не байтов. Вы можете применить для сортировки результатов выражения инструкцию ORDER BY, если это допускает ваша версия MySQL.

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

mysql> SELECT t, srcuser, FLOOR((size+1023)/1024) -> FROM mail WHERE size > 50000
-> ORDER BY 3;


+--------------------------+---------+--------------------------------+
| t | srcuser | FLOOR((size+1023)/1024) |
+--------------------------+---------+--------------------------------+
| 2001-05-11 10:15:08 | barb | 57 |
| 2001-05-14 14:42:21 | barb | 96 |
| 2001-05-12 12:48:13 | tricia | 191 |
| 2001-05-15 10:25:52 | gene | 976 |
| 2001-05-14 17:03:01 | tricia | 2339 |
+--------------------------+---------+--------------------------------+

mysql> SELECT t, srcuser, FLOOR((size+1023)/1024) AS kilobytes
-> FROM mail WHERE size > 50000
-> ORDER BY kilobytes;


+--------------------------+---------+-----------+
| t | srcuser | kilobytes |
+--------------------------+---------+-----------+
| 2001-05-11 10:15:08 | barb | 57 |
| 2001-05-14 14:42:21 | barb | 96 |
| 2001-05-12 12:48:13 | tricia | 191 |
| 2001-05-15 10:25:52 | gene | 976 |
| 2001-05-14 17:03:01 | tricia | 2339 |
+--------------------------+----------+----------+

Такой способ будет работать и для версий MySQL 3.23.2 и выше, но в них появляется дополнительная возможность размещения выражения непосредственно в инструкции ORDER BY:

mysql> SELECT t, srcuser, FLOOR((size+1023)/1024)
-> FROM mail WHERE size > 50000
-> ORDER BY FLOOR((size+1023)/1024);


+--------------------------+---------+---------------------------------+
| t | srcuser | FLOOR((size+1023)/1024) |
+--------------------------+---------+---------------------------------+
| 2001-05-11 10:15:08 | barb | 57 |
| 2001-05-14 14:42:21 | barb | 96 |
| 2001-05-12 12:48:13 | tricia | 191 |
| 2001-05-15 10:25:52 | gene | 976 |
| 2001-05-14 17:03:01 | tricia | 2339 |
+--------------------------+---------+----------------------------------+

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

• Легче написать инструкцию ORDER BY, используя псевдоним, чем заново вводя выражение (которое может быть весьма громоздким).

• Псевдоним может быть удобен при выводе – столбец получает понятное и осмысленное название.

Такое же ограничение накладывается на выражения в инструкции GROUP BY), и обходить его следует так же. Не забудьтеоб этом, если ваша версия MySQL старше, чем 3.23.2. Многие запросы далее в книге используют выражения в инструкциях ORDER BY и GROUP BY. Чтобы они заработали на ранних версиях сервера MySQL, вам придется переписать их, применяя только что изученный прием.

Добавлено: 22 Июля 2018 21:40:33 Добавил: Андрей Ковальчук