MySQL / 6. Сортировка результатов запроса

Сортировка одного набора значений и вывод другого

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

Решение
Никаких проблем. Вы можете использовать в инструкции ORDER BY столбцы, которые не указаны в списке вывода запроса.

Обсуждение
Инструкция ORDER BY может упорядочивать не только столбцы, перечисленные в списке вывода, но и «скрытые» (невыводимые) значения. Такой прием, как правило, используется, когда у вас есть значения, которые могут быть представлены несколькими способами, а вы хотите отображать один тип значений, а сортировать по другому. Например, вы можете захотеть вывести размеры почтовых сообщений не как количество байт (числа), а как
строки, то есть 103K для 103 Кбайт. Для преобразования количества байтов в такую строку используйте следующее выражение:

CONCAT(FLOOR((size+1023)/1024),'K')

Получившиеся значения – это строки, поэтому они упорядочиваются в лексическом, а не в числовом порядке. Если вы выполните для них сортировку, то значение 96K будет стоять после 2339K, несмотря на то, что представляет меньший размер:

mysql> SELECT t, srcuser,
-> CONCAT(FLOOR((size+1023)/1024),'K') AS size_in_K
-> FROM mail WHERE size > 50000
-> ORDER BY size_in_K;

+--------------------------+---------+------------+
| t                                  | srcuser | size_in_K |
+--------------------------+---------+------------+
| 2001-05-12 12:48:13 | tricia     | 191K       |
| 2001-05-14 17:03:01 | tricia     | 2339K     |
| 2001-05-11 10:15:08 | barb      | 57K        |
| 2001-05-14 14:42:21 | barb      | 96K         |
| 2001-05-15 10:25:52 | gene     | 976K       |
+--------------------------+---------+-----------+

Чтобы получить тот результат, который нам нужен, будем выводить строку, а для сортировки использовать числовое значение размера:

mysql> SELECT t, srcuser,
-> CONCAT(FLOOR((size+1023)/1024),'K') AS size_in_K
-> FROM mail WHERE size > 50000
-> ORDER BY size;

+--------------------------+---------+------------+
| t                                  | srcuser | size_in_K |
+--------------------------+---------+------------+
| 2001-05-11 10:15:08 | barb     | 57K          |
| 2001-05-14 14:42:21 | barb     | 96K          |
| 2001-05-12 12:48:13 | tricia     | 191K        |
| 2001-05-15 10:25:52 | gene    | 976K         |
| 2001-05-14 17:03:01 | tricia     | 2339K      |
+--------------------------+---------+------------+

Вывод в виде строк значений, отсортированных как числа, может помочь в ряде затруднительных ситуаций. Членам спортивных команд обычно присваивают номер, который присутствует у них на форме. Первое, что приходит в голову, – хранить его в числовом столбце. Но не торопитесь! Некоторым нравится номер ноль (0), а некоторым – двойной ноль (00). Если два таких игрока встретятся в одной команде, вы не сможете хранить их номера в числовом столбце, поскольку значения будут трактоваться как одинаковые.

Поэтому следует хранить номера как строки:

CREATE TABLE roster
(
name CHAR(30), # имя игрока
jersey_num CHAR(3) # номер на футболке
);

Тогда номера будут отображаться так, как вводятся, при этом 0 и 00 будут восприниматься как разные значения. К сожалению, хотя представление чисел в виде строк и решает проблему распознавания 0 и 00, оно вызывает проблемы другого рода. Пусть в команде есть такие игроки:

mysql> SELECT name, jersey_num FROM roster;

+-----------+---------------+
| name       | jersey_num |
+-----------+--------------+
| Lynne      | 29               |
| Ella          | 0                 |
| Elizabeth | 100             |
| Nancy      | 00               |
| Jean        | 8                 |
| Sherry     | 47               |
+-----------+--------------+

Проблема возникает, когда вы пытаетесь упорядочить членов команды по номеру. Если номера хранятся как строки, они будут отсортированы в лексическом порядке, который часто отличается от числового. Для игроков нашей команды это так и есть:mysql> SELECT name, jersey_num FROM roster ORDER BY jersey_num;

+-----------+------------+
| name       | jersey_num |
+-----------+---------------+
| Ella          | 0                  |
| Nancy     | 00                |
| Elizabeth | 100              |
| Lynne     | 29                 |
| Sherry    | 47                 |
| Jean       | 8                   |
+-----------+---------------+

Значения 100 и 8 стоят явно не на своих местах. Но здесь нет ничего сложного. Выводите строковые значения, но для сортировки используйте числа. Сложите значения jersey_num с нулем, чтобы вызвать преобразование строк в числа:

mysql> SELECT name, jersey_num FROM roster ORDER BY jersey_num+0;

+-----------+---------------+
| name        | jersey_num |
+-----------+---------------+
| Ella           | 0                  |
| Nancy      | 00                |
| Jean        | 8                  |
| Lynne      | 29                |
| Sherry     | 47                |
| Elizabeth | 100              |
+-----------+---------------+

Методику вывода одного значения и сортировки по другому также удобно использовать при выводе композитных значений, составленных из нескольких столбцов, которые упорядочиваются не так, как хотелось бы. Например, таблица mail представляет данные об отправителях сообщений в двух отдельных столбцах: srcuser и srchost. Если вы хотите вывести для отправителей адреса в формате srcuser@srchost, то можете получить такие значения, используя выражение:

CONCAT(srcuser,'@',srchost)

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

mysql> SELECT t, CONCAT(srcuser,'@',srchost) AS sender, size
-> FROM mail WHERE size > 50000
-> ORDER BY srchost, srcuser;

+---------------------------+----------------+-----------+
| t                                   | sender           | size         |
+--------------------------+-----------------+-----------+
| 2001-05-15 10:25:52 | gene@mars    | 998532   |
| 2001-05-12 12:48:13 | tricia@mars    | 194925   |
| 2001-05-11 10:15:08 | barb@saturn | 58274      |
| 2001-05-14 17:03:01 | tricia@saturn | 2394482 |
| 2001-05-14 14:42:21 | barb@venus  | 98151     |
+--------------------------+----------------+------------+

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

mysql> SELECT last_name, first_name FROM name
-> ORDER BY last_name, first_name;

+-------------+-------------+
| last_name | first_name |
+-------------+------------+
| Blue           | Vida           |
| Brown        | Kevin         |
| Gray          | Pete          |
| White         | Devon      |
| White         | Rondel      |
+-------------+------------+

Если же вы хотите выводить для каждого человека строку в виде «имя-пробел-фамилия», то можете начать запрос так:

SELECT CONCAT(first_name,' ',last_name) AS full_name FROM name ...

Но как теперь упорядочить записи так, чтобы они появлялись в алфавитном порядке фамилий? Будем выводить составные значения, а в инструкции ORDER BY ссылаться на составляющие этих значений:

mysql> SELECT CONCAT(first_name,' ',last_name) AS full_name
-> FROM name
-> ORDER BY last_name, first_name;

+-----------------+
| full_name        |
+-----------------+
| Vida Blue         |
| Kevin Brown    |
| Pete Gray       |
| Devon White  |
| Rondell White |
+-----------------+

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

Дело в том, что выражения в инструкции ORDER BY разрешены только начиная с версии MySQL 3.23.2.

Для того чтобы обойти это ограничение, покажем скрытый элемент – выражение, добавив его в список столбцов вывода, и будем ссылаться на него по номеру позиции столбца или при помощи псевдонима. Например, чтобы написать запрос, выводящий имена из таблицы names, начиная с самого длинного, в MySQL версии 3.23.2 и выше поступим так:

mysql> SELECT CONCAT(first_name,' ',last_name) AS name
-> FROM names-> ORDER BY LENGTH(CONCAT(first_name,' ',last_name)) DESC;

+-----------------+
| name              |
+-----------------+
| Rondell White |
| Kevin Brown   |
| Devon White  |
| Vida Blue        |
| Pete Gray      |
+----------------+

Чтобы переписать запрос для более ранних версий MySQL, поместим выражение в список столбцов вывода и используем для него псевдоним:

mysql> SELECT CONCAT(first_name,' ',last_name) AS name,
-> LENGTH(CONCAT(first_name,' ',last_name)) AS len
-> FROM names
-> ORDER BY len DESC;

+-----------------+----+
| name               | len |
+-----------------+----+
| Rondell White | 13  |
| Kevin Brown   | 11  |
| Devon White  | 11  |
| Vida Blue        | 9    |
| Pete Gray      | 9    |
+----------------+----+

Или сошлемся на дополнительный столбец вывода по номеру позиции:

mysql> SELECT CONCAT(first_name,' ',last_name) AS name,
-> LENGTH(CONCAT(first_name,' ',last_name)) AS len
-> FROM names
-> ORDER BY 2 DESC;

+---------------+------+
| name               | len |
+---------------+------+
| Rondell White | 13  |
| Kevin Brown    | 11 |
| Devon White   | 11 |
| Vida Blue         | 9   |
| Pete Gray        | 9   |
+---------------+------+

Какой бы способ ссылки вы ни выбрали, в выводе будет присутствовать столбец, который нужен там только для обеспечения возможности сортировки, а на самом деле вы абсолютно не заинтересованы в его выводе. Если вы выполняете запрос в программе mysql, то, к сожалению, ничего не можете сделать с этим дополнительным столбцом вывода. В ваших же собственных программах этот столбец не создает проблем. Да, он будет возвращен в результирующем множестве, но вы можете его игнорировать. Проиллюстрируем вышесказанное примером программы на Python. Выполняется запрос, имена отображаются, а длины имен отбрасываются:

cursor = conn.cursor (MySQLdb.cursors.DictCursor)
cursor.execute ("""
SELECT CONCAT(first_name,' ',last_name) AS full_name,
LENGTH(CONCAT(first_name,' ',last_name)) AS len
FROM name
ORDER BY len DESC
""")
for row in cursor.fetchall ():
print row["full_name"] # вывести имя, игнорировать длину
cursor.close ()

Статьи по MySQL на эту тему:

Размещение некоторых значений в начале или конце упорядоченного списка
Сортировка IP-адресов в числовом порядке
Сортировка в порядке, определенном пользователем
Сортировка значений ENUM
Сортировка и значения NULL