Сортировка по подстрокам переменной длины
Задача
Вы хотите выполнить сортировку, используя части столбца, не имеющие фиксированного местоположения внутри столбца.
Решение
Придумайте какой-нибудь способ идентификации тех частей, которые вы хотите извлечь, иначе у вас ничего не получится.
Обсуждение
Если подстрока, которую вы хотели бы использовать для сортировки, имеет переменную длину, то необходим надежный способ извлечения именно этой части строки. Чтобы посмотреть, что имеется в виду, создадим таблицу 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 |
+------------------+---------------------+
Выражения для извлечения строк выглядят достаточно запутанно, но по крайней мере в значениях столбцов, к которым мы их применяем, количество сегментов постоянно. Если речь идет о сортировке значений, число сегментов которых переменно, задача усложняется.