Как естественным образом сортировать строковые данные “X-Y”, сначала по X, а затем по Y?

#mysql #sql #natural-sort

#mysql #sql #естественная сортировка

Вопрос:

Учитывая эти данные:

 W18-40461
W19-1040
W20-4617
W20-100

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

 W20-4617
W20-100
W19-1040
W18-40461

Например:

 select theID 
from Table 
where theID 
order by lpad(theID, 9, 0) desc

1. какой результат вы хотели получить?

2. Я думаю, вы хотите использовать RPAD вместо LPAD .

3. Всегда ли префикс (перед дефисом) состоит из трех символов?

4. Как следует сортировать W55-66 и W55-7? Нужно ли сортировать справа от тире как целое число? То есть порядок 7 <66 или ’66’ <‘7’? Показанные данные слева от штриха не будут иметь этой потенциальной проблемы, если они имеют фиксированную ширину, например, от YY до 2099..

Ответ №1:

Предполагая, что части по обе стороны от - ограничены 2 цифрами и 5 цифрами соответственно, вы можете извлечь два числовых значения, используя SUBSTR (и LOCATE найти - между двумя числами), а затем LPAD дополнить каждое из этих значений до 2 и 5 цифр, чтобы их можно было отсортировать численно:

 SELECT *
FROM data
ORDER BY LPAD(SUBSTR(id, 2, LOCATE('-', id) - 2), 2, '0') DESC,
         LPAD(SUBSTR(id, LOCATE('-', id)   1), 5, '0') DESC

Вывод (для моего расширенного примера):

 id
W20-12457
W20-4617
W20-100
W19-1040
W18-40461
W4-2017

Демонстрация на db-fiddle

Если значения могут содержать более 2 или 5 цифр соответственно, просто измените вторые параметры LPAD на подходящие.

Ответ №2:

Я бы сделал это как:

 order by substring_index(col, '-', 1) desc,
         substring_index(col, '-', -1)   0 desc

Это упорядочивает по части перед дефисом в виде строки. И он преобразует часть после дефиса в число для целей сортировки.