Использование MySQL сортировки столбца varchar численно с приведением к беззнаковому, когда столбец может начинаться или заканчиваться буквами

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

Что касается предыстории, каждая карта (строка в базе данных) содержит информацию о годе, наборе карты, игроке на карте и номере карты (есть еще информация, но это все, что имеет значение здесь). Когда я вижу результаты, я хочу, чтобы все было отсортировано по году, затем по набору, затем по игроку, затем по номеру карты. Все, кроме номера карты, работает нормально, так как год — это просто целое число, а набор и игрок — это varchars, поэтому их легко сортировать. Тем не менее, номер карты - это то, с чем у меня возникают некоторые проблемы.

Столбец номера карты представляет собой varchar, поскольку номер карты может включать буквы, цифры и тире. Чаще всего номер карты представляет собой прямую цифру (например, 1, 2, 3, 4, 5), прямые буквы (Ex-A, Ex-B, Ex-C), число, за которым следует буква (1a, 1b). , 2, 3a, 3b, 3c) или букву, за которой следует цифра (A1, A2, A3, A4, A5). Вот как у меня в настоящее время настроена часть сортировки моей строки SQL:

order by year desc, cardset asc, subset asc, cast(cardNum as unsigned) asc;

Это прекрасно справляется с БОЛЬШИНСТВОМ вещей. Но у меня возникают проблемы, когда группа карт имеет одинаковые буквы в номере карты, а затем имеет номер. Я хочу, чтобы сортировка по существу игнорировала начальные буквы, а затем просто сортировала по номерам. Но иногда он делает это неправильно, особенно когда нужно отсортировать более 5 карточек.

В частности, он неправильно сортирует некоторые карты со следующими номерами карт в следующем порядке:

BCP61
BCP97
BCP32
BCP135

Когда это должно привести к:

BCP32
BCP61
BCP97
BCP135

В настоящее время он правильно сортирует прямые числа или числа, за которыми следуют буквы (например, 1, 2, 3, 4, 5 или 1, 2, 3a, 3b, 4, 5a, 5b). Я не знаю о каких-либо проблемах с неправильной сортировкой прямых букв, но в настоящее время у меня очень мало тестовых случаев, поэтому я не уверен, что это 100% или нет.

В дополнение к тому, что я не знаю, как изменить мой оператор сортировки SQL, не нарушая другие сортировки, я действительно не знаю, как он подходит к порядку, который он имеет для приведенного выше примера BCP. Есть мысли как исправить? Я думал о том, чтобы попытаться игнорировать буквы в номере карты, пока мы не перейдем к числам, но это вызовет серьезные проблемы с картами, в которых есть только буквы в номере карты. Так что я немного застрял.

Абсолютно худшее доходит до худшего, я, вероятно, мог бы разделить столбец с номером карты на 2 разных столбца: один для более описательной части, а другой для части, по которой я хочу сортировать. Это, вероятно, в конечном итоге будет работать нормально, но потребует много работы, чтобы вернуть все обратно!

Изменить. Вот еще информация, включая данные в моей БД (извините за форматирование, не знаю, как здесь делать таблицы):

| year | cardSet | subset                     | cardNum |
| 2016 | Bowman  |                            | 52      |
| 2016 | Bowman  |                            | 54      |
| 2016 | Bowman  |                            | 147     |
| 2016 | Bowman  | Chrome Prospects           | BCP32   |
| 2016 | Bowman  | Chrome Prospects           | BCP61   |
| 2016 | Bowman  | Chrome Prospects           | BCP97   |
| 2016 | Bowman  | Chrome Prospects           | BCP135  |
| 2016 | Topps   |                            | 1       |
| 2016 | Topps   |                            | 2a      |
| 2016 | Topps   |                            | 2b      |
| 2016 | Topps   |                            | 3       |

Я ожидаю, что моя сортировка выдаст результаты в следующем порядке:

2016 Боумен 52
2016 Боумен 54
2016 Боумен 147
2016 Bowman Chrome Перспективы BCP32
2016 Bowman Chrome Перспективы BCP61
2016 Bowman Chrome Перспективы BCP97
2016 Bowman Chrome Prospects BCP125
2016 Топпс 1
2016 Топпс 2а
2016 Топпс 2b
2016 Топпс 3

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

2016 Боумен 52
2016 Боумен 54
2016 Боумен 147
2016 Bowman Chrome Перспективы BCP62
2016 Bowman Chrome Перспективы BCP97
2016 Bowman Chrome Перспективы BCP32
2016 Bowman Chrome Prospects BCP125
2016 Топпс 1
2016 Топпс 2а
2016 Топпс 2b
2016 Топпс 3

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

Я попытался использовать трюк length() в комментариях, чтобы часть сортировки моего SQL была:

order by year desc, cardset asc, subset asc, length(cardNum), cardNum asc

Это действительно устраняет проблему, которую я описал выше, но портит часть «Topps» в моем примере — карты с буквами, следующими за цифрой, будут помещены последними, несмотря ни на что. Вот порядок, который я получаю с этим видом:

2016 Боумен 52
2016 Боумен 54
2016 Боумен 147
2016 Bowman Chrome Перспективы BCP32
2016 Bowman Chrome Перспективы BCP61
2016 Bowman Chrome Перспективы BCP97
2016 Bowman Chrome Prospects BCP125
2016 Топпс 1
2016 Топпс 3
2016 Топпс 2а
2016 Топпс 2b

Hollywood 03.02.2018 источник

comment

См. Почему я должен предоставлять MCVE для того, что мне кажется очень простым SQL-запросом - Strawberry 03.02.2018

comment

Моя ошибка в том, что я не включил более достоверные данные ранее. Есть ли что-то, чего сейчас не хватает, что было бы полезно добавить? - Hollywood 03.02.2018

comment

Ничего, что не было уже освещено в моем предыдущем комментарии. - Strawberry 03.02.2018

comment

Вы ищете естественную сортировку: stackoverflow.com/questions/153633/natural-sort-in-mysql< /а> - Salman A 03.02.2018

comment

Есть ли какой-либо другой пример, который вам не хватает, то есть что-то, кроме цифр-только, цифр-строки, строк-цифр, цифр-строки-цифр? - Salman A 04.02.2018

Ответы (2)

arrow_upward
5
arrow_downward

MariaDB 10 и MySQL 8 поддерживает REGEXP_REPLACE. Используя это, вы можете определить пользовательскую функцию:

DROP FUNCTION IF EXISTS zerofill_numbers;
CREATE FUNCTION zerofill_numbers(str TEXT, len TINYINT)
    RETURNS text
    NO SQL
    DETERMINISTIC
    RETURN REGEXP_REPLACE(
        REGEXP_REPLACE(str, '(\\d+)', LPAD('\\1', len+2, 0)),
        REPLACE('0*(\\d{$len})', '$len', len),
        '\\1'
    );

Теперь, учитывая следующие тестовые данные:

DROP TABLE IF EXISTS `strings`;
CREATE TABLE IF NOT EXISTS `strings` (`str` text);
INSERT INTO `strings` (`str`) VALUES
    ('Bowman 52'),
    ('Bowman 54'),
    ('Bowman 147'),
    ('Bowman Chrome Prospects BCP32'),
    ('Bowman Chrome Prospects BCP61'),
    ('Bowman Chrome Prospects BCP97'),
    ('Bowman Chrome Prospects BCP125'),
    ('Topps 1'),
    ('Topps 3'),
    ('Topps 2a'),
    ('Topps 2b'),
    ('v9.9.3'),
    ('v9.10.3'),
    ('v11.3.4'),
    ('v9.9.11'),
    ('v11.3'),
    ('0.9'),
    ('0.11'),
    ('s09'),
    ('s11'),
    ('s0'),
    ('v9.0.1');

Мы можем отсортировать его с помощью:

SELECT s.str
FROM strings s
ORDER BY zerofill_numbers(s.str, 10)

Вот результат:

0.9
0.11
Bowman 52
Bowman 54
Bowman 147
Bowman Chrome Prospects BCP32
Bowman Chrome Prospects BCP61
Bowman Chrome Prospects BCP97
Bowman Chrome Prospects BCP125
s0
s09
s11
Topps 1
Topps 2a
Topps 2b
Topps 3
v9.0.1
v9.9.3
v9.9.11
v9.10.3
v11.3
v11.3.4

Функция будет заполнять любое число в строке нулями до тех пор, пока оно не будет иметь заданную длину.

Примечание 1. Это не приведет к правильной сортировке десятичных чисел (см. 0.9 и 0.11). Вы также не должны пытаться использовать его для чисел со знаком.

Примечание 2. Эта функция основана на следующем ответе: https://stackoverflow.com/a/46099386/5563083. Так что, если вы найдете этот ответ полезным, проголосуйте за источник.

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

SELECT *
FROM strings
ORDER BY
  REGEXP_REPLACE(REGEXP_REPLACE(str, '(\\d+)', LPAD('\\1', 10+2, 0)), '0*(\\d{10})', '\\1')

Paul Spiegel 03.02.2018

arrow_upward
0
arrow_downward

Для ваших демонстрационных данных вы можете использовать трюк length():

order by year desc, cardset asc, subset asc,
         length(cardNum),
         cardNum asc;

Вы не предоставляете много образцов данных. Это было бы общим решением, если бы в «подмножестве» все номера карт имели одинаковую форму (и не имели начальных нулей в числовой части).

Gordon Linoff 03.02.2018

comment

Извините, я отредактирую дополнительную информацию. Попытка сбалансировать его чистоту, поскольку данные могут сбивать с толку тех, кто не привык к спортивным карточкам, наряду с предоставлением достаточного количества данных для получения надежных ответов. Что именно делает функция length()? Просто скажите, сколько символов в чем-то? Если да, то как это исправить сортировку? BCP91, BCP61 и BCP32 имеют 5 символов (если я правильно понимаю), так как же он узнает, что сначала 32, затем 61, затем 91? - Hollywood; 03.02.2018

comment

Поэтому опробовал ваше решение, и оно исправляет пример BCP, с которым у меня были проблемы, но также вызывает проблемы при сортировке (1, 2, 3a, 4, 5). Он сортирует их по (1, 2, 4, 5, 3a), что имеет смысл, но возникают проблемы с поиском чего-то, что правильно охватывает все случаи. - Hollywood; 03.02.2018

Использование MySQL сортировки столбца varchar численно с приведением к беззнаковому, когда столбец может начинаться или заканчиваться буквами

Ответы (2)

Вопросы по теме