Неудачные попытки естественной сортировки в MySQL

Я пытаюсь добиться естественной / буквенно-цифровой сортировки в MySQL и безуспешно. Я прочитал несколько статей из Stack и различные статьи по теме, которую я нашел в поиске Google. Вот основной запрос, с которого я начал:

select VideoTitle, VideoID
from Filters
where VideoSource = 'Netflix'
and IMDBSeries = 'tt0367279'
and PublishDate is not null
ORDER BY VideoTitle

Этот запрос дает этот набор результатов:

Arrested Development Season 1: Ep. 1 Pilot
Arrested Development Season 1: Ep. 10 Pier Pressure
Arrested Development Season 1: Ep. 2 Top Banana
Arrested Development Season 1: Ep. 3 Bringing Up Buster
Arrested Development Season 1: Ep. 4 Key Decisions
Arrested Development Season 1: Ep. 5 Charity Drive
Arrested Development Season 1: Ep. 6 Visiting Ours
Arrested Development Season 1: Ep. 7 In God We Trust
Arrested Development Season 1: Ep. 8 My Mother, the Car
Arrested Development Season 1: Ep. 9 Storming the Castle

Обратите внимание, что эта строка вышла из строя с точки зрения естественной сортировки:

Арестованный Сезон Развития 1: Эп. 10 Пирс Давление

Основываясь на сообщениях / статьях, которые я прочитал, я также попробовал эти подходы с соответствующими наборами результатов:

select VideoTitle
from Filters
where VideoSource = 'Netflix'
and IMDBSeries = 'tt0367279'
and PublishDate is not null
ORDER BY CAST(VideoTitle AS UNSIGNED), VideoTitle

Arrested Development Season 1: Ep. 1 Pilot
Arrested Development Season 1: Ep. 10 Pier Pressure
Arrested Development Season 1: Ep. 2 Top Banana
Arrested Development Season 1: Ep. 3 Bringing Up Buster
Arrested Development Season 1: Ep. 4 Key Decisions
Arrested Development Season 1: Ep. 5 Charity Drive
Arrested Development Season 1: Ep. 6 Visiting Ours
Arrested Development Season 1: Ep. 7 In God We Trust
Arrested Development Season 1: Ep. 8 My Mother, the Car
Arrested Development Season 1: Ep. 9 Storming the Castle

select VideoTitle
from Filters
where VideoSource = 'Netflix'
and IMDBSeries = 'tt0367279'
and PublishDate is not null
ORDER BY LENGTH(VideoTitle), VideoTitle

Arrested Development Season 1: Ep. 1 Pilot
Arrested Development Season 1: Ep. 2 Top Banana
Arrested Development Season 1: Ep. 4 Key Decisions
Arrested Development Season 1: Ep. 5 Charity Drive
Arrested Development Season 1: Ep. 6 Visiting Ours
Arrested Development Season 1: Ep. 10 Pier Pressure
Arrested Development Season 1: Ep. 7 In God We Trust
Arrested Development Season 1: Ep. 3 Bringing Up Buster
Arrested Development Season 1: Ep. 8 My Mother, the Car
Arrested Development Season 1: Ep. 9 Storming the Castle

select VideoTitle
from Filters
where VideoSource = 'Netflix'
and IMDBSeries = 'tt0367279'
and PublishDate is not null
ORDER BY VideoTitle + 0 ASC

Arrested Development Season 1: Ep. 1 Pilot
Arrested Development Season 1: Ep. 9 Storming the Castle
Arrested Development Season 1: Ep. 8 My Mother, the Car
Arrested Development Season 1: Ep. 7 In God We Trust
Arrested Development Season 1: Ep. 6 Visiting Ours
Arrested Development Season 1: Ep. 5 Charity Drive
Arrested Development Season 1: Ep. 4 Key Decisions
Arrested Development Season 1: Ep. 3 Bringing Up Buster
Arrested Development Season 1: Ep. 2 Top Banana
Arrested Development Season 1: Ep. 10 Pier Pressure

Есть идеи?

Источник

3 ответа

Решение

Лучшее решение - хранить заголовок шоу, сезон, номер эпизода и заголовок эпизода в отдельных столбцах.

В этом случае вы можете выполнить некоторые манипуляции со строками, чтобы получить то, что вам нужно.

ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(VideoTitle, ': ', -1), ' ', 2), ' ', -1)+0

Это такое хрупкое решение.

Источник

Если вы действительно хотите, вы можете отсортировать это по частям:

order by substring_index(VideoTitle, ' Season ', 1),
         substring_index(VideoTitle, ' Season ', -1) + 0,
         substring_index(VideoTitle, ': Ep. ', -1) + 0

Это предполагает, что строка ' Season ' : Ep. встречаются только один раз в каждом заголовке.

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

order by substring_index(VideoTitle, ': Ep. ', 1),
         substring_index(VideoTitle, ': Ep. ', -1) + 0
Источник

Если бы создать новые столбцы для улучшения поиска в вашей модели, я бы выбрал более элегантное решение, чем разбивать строку на части.

select VideoTitle 
  from (select VideoTitle, 
               VideoTitle REGEXP '.+Ep\\. [0-9]{2}.+' vd2dig 
          from videos
       ) sub
 order by vd2dig, VideoTitle;

Это по существу сделает VideoTitle когда две цифры 1 и когда у него будет одна цифра, это будет 0 поэтому я заказываю по этому столбцу, а затем естественный порядок VideoTitle

Посмотреть это работает здесь: http://sqlfiddle.com/

Источник
Другие вопросы по тегам