О сортировке данных с помощью SQL запросов, думаю, знают все web разработчики.
Достаточно указать в запросе предложение ORDER BY с нужными параметрами и вы получите желаемый результат.
Параметры задавать тоже несложно. Просто перечисляете через запятую столбцы по которым нужно выполнить сортировку и указываете её направление (по возрастанию (ASC) или по убыванию (DESC)).
Т.е. запрос, выполняющий сортировку может выглядеть примерно следующим образом.
SELECT * FROM articles ORDER BY a_title ASC, a_date DESC
В этом случае записи из таблицы articles будут отсортированы в возрастающем порядке по полю a_title, а для записей у которых совпадают значения в поле a_title – по полю a_date в убывающем порядке.
Именно в такой форме используются возможности сортировки в большинстве случаев. Но встречаются ситуации, в которых нужно использовать более сложные правила.
Сортировка с учетом типа данных
Представьте, что у вас есть две таблицы. Первая содержит какие-нибудь записи (статьи, новости, товары и т.п.), а вторая – метаданные для этих записей. Метаданные могут содержать какую угодно информацию, например, рейтинг новости или цвет товара.
При этом таблицы будут иметь приблизительно следующую структуру.
Подобные решения используются во многих CMS, т.к. авторы не знают какие именно метаданные будет использовать разработчик сайта и предоставляют ему возможность создавать их в неограниченном количестве.
Но что произойдет если мы попытаемся выполнить сортировку по метаданным?
Такую сортировку можно выполнить с помощью следующего запроса
SELECT * FROM articles AS a LEFT JOIN metadata AS m ON a.a_id=m.m_article_id WHERE m.m_name="color"
Этот запрос выведет все записи из таблицы articles для которых создано мета-поле color.
Очевидно, что тип поля m_value должен быть текстовым, т.к. заранее неизвестно что будет храниться в метаданных.
При этом числовые данные будут отсортированы не правильно. Дело в том, что ORDER BY сравнивает значения с учетом типа поля и, например, при сортировке по возрастанию вы получите следующий ряд значений: «1», «10», «2», «3» и т.д.
Т.е. при выполнении такого запроса нужно указать, что поле m_value необходимо считать числовым. Делается это следующим образом.
SELECT * FROM metadata ORDER BY (m_value+0)
Обратная операция (сортировка числового поля по правилам текстового) записывается немного сложнее.
SELECT left(a_id, 20) AS id_str FROM articles ORDER BY id_str
Функция left возвращает строку, содержащую первые N символов из строки, указанной в первом параметре. Количество символов (N) задается во втором параметре. В данном случае будут выбраны первые 20 символов (достаточно чтобы преобразовать 8-байтное целое число в строку). Т.е. движок MySQL выполнит сортировку по строке, полученной из значения числового поля.
Хочу предупредить, что несмотря на то, что данные методы могут быть удобны в ряде ситуаций, их использование приводит к снижению скорости выполнения SQL запросов. Поэтому злоупотреблять ими не стоит. С другой стороны, сортировка с помощью PHP (или любого другого языка) также займет какое-то время.
Сортировка текста с учетом регистра
Для текстовых полей сортировка выполняется без учета регистра. В большинстве случаев это правильно, т.к. в таблице символов прописная «А» идет после строчной «я». Т.е. отсортированные с учетом регистра строки будут расположены в следующем порядке.
1. «Язык запросов»
2. «Язык программирования»
3. «Язык Запросов»
Но если всё-таки сортировку нужно выполнить с учетом регистра, просто добавьте оператор BINARY перед именем поля.
SELECT * FROM articles ORDER BY BINARY a_title
Сортировка по фрагменту строки
Если вам приходится использовать этот прием, то, скорее всего, ваша база данных не соответствует перовой нормальной форме. Поэтому, подумайте, может лучше пересмотреть её структуру?
Но, в любом случае, вы можете использовать функцию SUBSTRING_INDEX для выбора подстроки и последующей сортировки. Например,
SELECT SUBSTRING_INDEX(a_title, ' ', -1) AS at FROM wp_5_posts ORDER BY at
Сортировка записей по заданному списку значений
Представьте, что у вас есть записи, которые каким-то образом связаны с временем года. И вы храните эту информацию в одном из столбцов таблицы.
Очевидно, что, используя стандартный вариант сортировки (по алфавиту), расположить сезоны порядке «весна», «лето», «осень», «зима» не получится. Естественно, можно каждому времени года присвоить свой код, но есть и другой вариант решения – использовать функцию FIELD.
SELECT * FROM articles ORDER BY FIELD(a_season, "весна","лето","осень","зима")
Эта функция ищет значение, указанное в первом параметре, среди значений, перечисленных в остальных параметрах, и возвращает его порядковый номер. При выполнении запроса в первый параметр функции FIELD будут передаваться значения из поля a_season и, таким образом, записи будут отсортированы в заданном нами порядке.
Сортировка строк по их длине
Эта возможность используется довольно редко, т.к. обычно длина строки смысловой нагрузки не несет. Тем не менее, в некоторых случаях может пригодиться.
SELECT * FROM articles ORDER BY CHAR_LENGTH(a_title)
Как видите, «фокус» заключается в использовании функции CHAR_LENGTH, которая определяет количество символов в строке.
Заключение
Думаю, глядя на последние несколько рецептов, вы понимаете, что подобным образом можно использовать практически все функции MySQL. Мне будет интересно, если вы поделитесь примерами из своей практики 😉
Интересное в Интернете
Заказ свадебных фотоальбомов с доставкой по РФ и СНГ
