SUBSTRING_INDEX сортирует число с символом и символом

Question

Я использую MySQL 5.5 для выполнения своего запроса. Я столкнулся с проблемой сортировки числа -перед символом. Ниже приведен пример моей таблицы, в которой я хочу отсортировать столбец number_with_nameс условием:

Название таблицы: test123

+-----+--------------------+
| id  | number_with_name   |
+-----+--------------------+    
| 1   |   200-2 David      |
| 2   |   200-2-2 Peter    |
| 3   |   200-2-2-9 James  |
| 4   |   200 Robert       |
| 5   |   200-2-3 Siva     |
| 6   |   200-2-5 Denny    |
| 8   |   200-2-9 Rose     |
| 9   |   200-3 Kiki       |
| 10  |   100-3-2 Viva     |
| 11  |   100-3-15 Proton  |
| 12  |   100-3-6 Saga     |
| 13  |   100 Liver        |
| 14  |   100-3 Shawn      |
| 15  |   100-3-5-1 Kola   |
| 16  |   100-3-5-8 Frankie|
| 17  |   100-3-5 Jala     |
+----+---------------------+

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

+-----+--------------------+
| id  | number_with_name   |
+-----+--------------------+  
| 13  |   100 Liver        |
| 14  |   100-3 Shawn      |
| 10  |   100-3-2 Viva     |
| 17  |   100-3-5 Jala     |
| 15  |   100-3-5-1 Kola   |
| 16  |   100-3-5-8 Frankie|
| 12  |   100-3-6 Saga     |
| 11  |   100-3-15 Proton  |
| 4   |   200 Robert       |
| 1   |   200-2 David      |
| 2   |   200-2-2 Peter    |
| 3   |   200-2-2-9 James  |
| 5   |   200-2-3 Siva     |
| 6   |   200-2-5 Denny    |
| 8   |   200-2-9 Rose     |
| 9   |   200-3 Kiki       |
+----+---------------------+

Я использовал ниже SQL для сортировки, но это не работает.

SELECT * from test123 order by SUBSTRING_INDEX(number_with_name, '-', -1) + 0 asc

Результат ответа Акиры для реального теста:

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

Answer 1

SELECT *
FROM test
ORDER BY SUBSTRING_INDEX(number_with_name, '-', 1) + 0
       , CASE WHEN LENGTH(number_with_name) - LENGTH(REPLACE(number_with_name, '-', '')) > 0
              THEN SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, '-', 2), '-', -1) + 0
              ELSE 0 
              END
       , CASE WHEN LENGTH(number_with_name) - LENGTH(REPLACE(number_with_name, '-', '')) > 1
              THEN SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, '-', 3), '-', -1) + 0
              ELSE 0 
              END

скрипка

Answer 2

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

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

SELECT *
FROM test
ORDER BY LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 1)) ASC,
         SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 1),
         LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 2)) ASC,
         SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 2),
         LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 3)) ASC,
         SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 3),
         LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 4)) ASC,
         SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 4)

Это сортирует до четырех компонентов. Единственная уловка здесь - вложенная substring_index(). Это ограничивает сравнение только начальным числом, что, кажется, является намерением.

Вот скрипка db <>.