Управление порядком вывода запроса с помощью соединения

Задача
Вы хотите упорядочить вывод запроса, используя ту его характеристику, которую невозможно указать в инструкции ORDER BY. Например, вы хотите отсортировать строки по подгруппам, выводя первыми те группы, в которых больше всего строк, а последними – группы с наименьшим количеством строк. Но «количество строк группы» – это не свойство отдельных строк, поэтому вы не можете упорядочивать по нему.

Решение
Извлеките информацию о порядке вывода и сохраните в другой таблице. Затем объедините исходную таблицу с производной, используя производную для управления порядком сортировки.

Обсуждение
Обычно при сортировке результата запроса применяется инструкция ORDER BY (или GROUP BY) для указания имени столбца или столбцов, по которым нужно производить сортировку. Но иногда требуется выполнить сортировку по значениям, которые не содержатся в упорядочиваемых строках. Именно так дело обстоит с использованием групповых характеристик для упорядочивания строк. Следующий пример использует записи таблицы driver_log для иллюстрации вышесказанного:

mysql> SELECT * FROM driver_log ORDER BY id;

+-------+-------+--------------+-------+
| rec_id | name | trav_date | miles |
+-------+-------+--------------+-------+
| 1 | Ben | 2001-11-30 | 152 |
| 2 | Suzi | 2001-11-29 | 391 |
| 3 | Henry | 2001-11-29 | 300 |
| 4 | Henry | 2001-11-27 | 96 |
| 5 | Ben | 2001-11-29 | 131 |
| 6 | Henry | 2001-11-26 | 115 |
| 7 | Suzi | 2001-12-02 | 502 |
| 8 | Henry | 2001-12-01 | 197 |
| 9 | Ben | 2001-12-02 | 79 |
| 10 | Henry | 2001-11-30 | 203 |
+-------+--------+--------------+------+

Такой запрос сортирует записи по столбцу идентификаторов, содержащемуся в строках.


А если нам нужно вывести список и упорядочить его на основе суммарного значения, которое не присутствует в строках? Это немного сложнее. Предположим, что вы хотите вывести записи каждого водителя в порядке возрастания даты, при этом начать с тех водителей, которые проехалисамое больше расстояние. Выполнить подобную операцию при помощи суммарного запроса невозможно, так как тогда нельзя будет вывести записи для отдельных водителей. Но и обойтись без суммарного запроса невозможно, так как для сортировки необходимо итоговое значение. Выйдем из затруднительного положения, создав новую таблицу, содержащую суммарные значения, и объединим ее с исходной. Так мы сможем и вывести индивидуальные записи, и отсортировать их по суммарным значениям.

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

mysql> CREATE TABLE tmp
-> SELECT name, SUM(miles) AS driver_miles FROM driver_log GROUP BY name;

Сформируем значения, которые необходимы для размещения имен (name) в нужном порядке:

mysql> SELECT * FROM tmp ORDER BY driver_miles DESC;

+-------+--------------+
| name | driver_miles |
+-------+--------------+
| Henry | 911 |
| Suzi | 893 |
| Ben | 362 |
+-------+--------------+

Затем используем значения name для соединения суммарной таблицы с таблицей driver_log, пользуясь значениями driver_miles для упорядочивания результата.


Приведенный ниже запрос выводит общее количество километров. Это сделано только для того, чтобы было понятнее, как сортируются записи, нет никакой необходимости в их выводе. Нужны они только в инструкции ORDER BY.

mysql> SELECT tmp.driver_miles, driver_log.*
-> FROM driver_log, tmp
-> WHERE driver_log.name = tmp.name
-> ORDER BY tmp.driver_miles DESC, driver_log.trav_date;

+--------------+--------+--------+--------------+-------+
| driver_miles | rec_id | name | trav_date | miles |
+--------------+--------+--------+--------------+-------+
| 911 | 6 | Henry | 2001-11-26 | 115 |
| 911 | 4 | Henry | 2001-11-27 | 96 |
| 911 | 3 | Henry | 2001-11-29 | 300 |
| 911 | 10 | Henry | 2001-11-30 | 203 |
| 911 | 8 | Henry | 2001-12-01 | 197 |
| 893 | 2 | Suzi | 2001-11-29 | 391 |
| 893 | 7 | Suzi | 2001-12-02 | 502 |
| 362 | 5 | Ben | 2001-11-29 | 131 |
| 362 | 1 | Ben | 2001-11-30 | 152 |
| 362 | 9 | Ben | 2001-12-02 | 79 |
+--------------+--------+--------+--------------+-------+.



Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Вставка записей в таблицу, включающую значения из другой
Вывод списков для записей «главная-подчиненная» и итогов
Вычисление разности между последовательными строками
Вычисление рейтинга команд
Выявление и удаление несвязанных записей