Сортировка MySQL с буквенно-цифровым префиксом

У меня есть база данных со столбцом, содержащим следующие данные:

aaa-1
aaa-2
aaa-3
...
aaa-10
aaa-11
...
aaa-100
aaa-101
...
aaa-1000

Когда я запрашиваю и сортирую данные в порядке возрастания, я получаю:

aaa-1
aaa-10
aaa-11
...
aaa-100
aaa-101
...
aaa-1000
...
aaa-2
...
aaa-3

Действительно ли это правильный (машинный) способ сортировки? Не получается ли заказ из-за префикса aaa-? Как мне отсортировать это так, как это сделал бы человек (то есть что-то похожее на первый фрагмент)?

P.S. Если проблема заключается в префиксе, есть ли способ удалить его и отсортировать, используя только числовой компонент?

P.P.S. Мне было предложено просто изменить свои данные и добавить ведущие нули, такие как aaa-0001 и aaa-0002 и т. Д. Однако я не хочу использовать этот метод, поскольку каждый раз, когда список увеличивается на порядок 10, мне придется переформатируйте этот столбец.

Спасибо всем заранее! :)


mysql sorting columnsorting
person doraemond    schedule 02.10.2015    source источник

Ответы (4)


arrow_upward
2
arrow_downward

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

SELECT mytable.*,
    CAST(SUBSTRING_INDEX(mycolumn, '-', - 1) AS UNSIGNED) mycolumnintdata
FROM
    mytable
ORDER BY mycolumnintdata;

Если есть выражения, которые не соответствуют числу, функция CAST вернет 0, и эти записи будут отображаться первыми. При необходимости вы можете справиться с этим отдельно.

person James Jithin    schedule 02.10.2015
comment
Спасибо! Это помогло мне :) - person doraemond; 03.10.2015
comment
Классно @doraemond! Спасибо, что приняли ответ. Удачного программирования! - person James Jithin; 03.10.2015
comment
@James Jithin Мне это нравится. Но, к сожалению, он мне нужен в подготовленном операторе, чтобы присоединиться к другим моим запросам, которые уже находятся в формате подготовленного оператора. Я застрял в bind_result Предупреждение: mysqli_stmt :: bind_result (): количество переменных связывания не соответствует количеству полей ... - person Mike; 18.09.2018
comment
@James Jithin Когда я готовил код, я действительно нашел ответ. Решение состоит в том, чтобы добавить еще одну переменную в bind_result (x, x, x, $ mycolumnintdata). Спасибо за вопрос. Если бы вы не спросили, я бы не нашел ответа. - person Mike; 18.09.2018

arrow_upward
1
arrow_downward

У меня была аналогичная проблема, и трюк, который помог мне, заключался в следующем.

* "ЗАКАЗАТЬ ПО ДЛИНЕ (имя_столбца), имя_столбца

Если нечисловая часть значения имеет одинаковую длину, выполняется сортировка от 1 до 10, от 10 до 100 и т. Д. "*

как указано Андреасом Бергстремом на этот вопрос.

Надеюсь, это кому-то поможет.

person Enomatix24    schedule 28.03.2018

arrow_upward
0
arrow_downward

это алфавитный порядок,
вам нужен числовой порядок,
для этого вы должны в предложении ORDER BY

  1. обрезать часть "ааа"

  2. преобразовать его в число

    convert (ПОДСТРОКА (val, 3), целое)

person Mattia Caputo    schedule 02.10.2015
comment
Теперь я вижу, что столбец является строкой и рассматривается как таковая. Таким образом, сначала будут отсортированы более короткие строки. Действительно ли это ограничение базы данных в том смысле, что у нее нет глубокого понимания содержащейся в ней информации? - person doraemond; 03.10.2015

arrow_upward
0
arrow_downward

Я дам вам образец сортировки. Не на основе вашей выборки данных, но это может вам помочь.

Допустим, у вас есть такие данные:

id
----
1
2
6
10
13

когда вы это сделаете, ORDER BY id ASC вернется:

id
----
1
10
13
2
6

Предлагаю использовать LPAD. Этот запрос: SELECT LPAD('12',5,'0') return 00012

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

SELECT * FROM TABLE
ORDER BY LPAD(ID,7,'0') ASC

На основании ваших данных. SELECT SUBSTR('aaa-100',5,LENGTH('aaa-100') - 3) return 100
Итак, SELECT LPAD( SUBSTR('aaa-100',5,LENGTH('aaa-100') - 3), 7, '0') return 00000100

Таким образом, вы можете комбинировать строковые функции, такие как SUBSTR и LPAD. Есть какие-нибудь подсказки?

person RubahMalam    schedule 02.10.2015
comment
В вашем примере не будет ли этот столбец сортироваться правильно даже без использования LDAP, поскольку это просто числовые данные? - person doraemond; 02.10.2015
comment
Да, теперь я понимаю, как работает LPAD, спасибо! Однако мне интересно, работает ли использование вашего метода или метода Джеймса быстрее. - person doraemond; 03.10.2015