2.22: В какой строке MySQL находится максимальное значение по группе?
Главная Страница » Книги по PHP » MySQL уроки для начинающих с нуля » В какой строке находится максимальное значение по группе
Для каждого изделия найдите торгового агента с наиболее высокой ценой.
В MySQL проще всего добиться такого эффекта так:
- Получите список из записей (изделие, максимальная цена).
- Для каждого изделия получите из таблицы соответствующие строки, которые имеют максимальную цену.
Это может быть легко выполнено через временную таблицу, которая нужна только для хранения промежуточного набора данных. Это довольно сложная последовательность действий, поэтому рассмотрим ее по шагам. Сначала создайте в базе данных временную таблицу:
CREATE TEMPORARY TABLE tmp (article INT(4) UNSIGNED ZEROFILL
DEFAULT "0000" NOT NULL, price DOUBLE(16,2)
DEFAULT "0.00" NOT NULL);
Как видите, во временной таблице всего два столбца: один для кода изделия изделия (article), другой для цены (price). Ключевое слово TEMPORARY означает временно созданную таблицу для хранения промежуточных данных.
Теперь надо проследить вот за каким сложным моментом. Дело в том, что MySQL представляет собой многопользовательскую систему. Это значит, что пока вы работаете с таблицей, с ней может работать кто-то еще (таких «кого-то еще» может быть сколько угодно). Друг о друге вы не знаете ничего, равно как и о том, кто и что с таблицей делает. Знать это вам незачем.
Но вот проблема: что будет, если в тот момент, когда вы получаете из таблицы данные для своего анализа, кто-то возьмет и поменяет их? Ни к чему страшному это не приведет, но вы получите устаревшие данные, изменений вы не увидите. Значит, надо помешать внести изменения до тех пор, пока вы не закончите расчеты. Это делается с помощью блокировки. Тема блокировок обширна и сложна, а потому рассматривается в последующих главах.
Но сейчас рассмотрим наиболее простой вариант. Итак, надо блокировать таблицу shop. Нам не требуется сделать таблицу совсем недоступной. Нужно только, чтобы никто не мог вносить в нее изменения. А вот читать таблицу другие пользователи смогут. Приступим:
LOCK TABLES shop read;
Если вы не используете таблицу TEMPORARY, вы должны также блокировать и таблицу tmp.
Слово read в этой команде означает, что вы разрешили другим пользователям сервера читать таблицу. А вот тут понятие потока может привести к недопониманию. Дело в том, что блокировка таблицы распространяется на все потоки, за исключением того, который эту блокировку создал.
Казалось бы: ну и что? А вот что: если вы открыли несколько копий клиента и установили не одно соединение с сервером, то блокировка таблицы распространится и на все ваши соединения, исключая то, в котором вы блокировку поставили. Соответственно, и снять блокировку вы сможете только в том же самом соединении.
Неважно, что вы регистрировались на сервере под одним и тем же логином во всех соединениях. Важно то, что блокировка привязана к конкретному соединению, а не к пользователю. Такая ситуация часто приводит начинающих пользователей к ошибке: они ставят блокировку на одном соединении, а снять пытаются из другого. Не выйдет, сервер не позволит.
Теперь начинается то, ради чего мы блокировали таблицу. Выберем из нее нужные данные и наполним ими подготовленную временную таблицу:
INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;
Как видите, здесь применен еще один хитрый прием. Вместо того, чтобы перечислять в операторе INSERT вводимые значения (используя слово VALUES), мы подставили оператор SELECT непосредственно вместо них. Что при этом произойдет? А вот что: оператор SELECT найдет и вернет строки, но вместо отображения на экране они попадут прямо в оператор INSERT, который вставит их в таблицу tmp. Таким образом, один запрос может быть вложен в другой (здесь SELECT вложили в INSERT). Возьмите на заметку для последующего использования.
Вот теперь найдем максимальную цену. В запросе используются две таблицы (shop и tmp), а раз так, то применяются имена столбцов с указанием таблицы, из которой надо извлечь данные:
SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;
Отлично, данные получены. Теперь дадим возможность другим пользователям работать с блокированной таблицей в полном объеме (а не только читать ее):
UNLOCK TABLES;
Эта команда сняла любые блокировки, установленные текущим потоком, со всех заблокированных вами таблиц. Подробнее о блокировках мы еще будем говорить позже (и немало: тема сложная), а пока удалим ставшую лишней временную таблицу со всеми данными в ней:
DROP TABLE tmp;
Команда DROP предназначена для удаления чего-либо. Поскольку сейчас надо удалить таблицу, указано ключевое слово TABLE (таблица), а затем ее имя. Вообще учите английский язык! SQL отличается от языков программирования как раз тем, что работаете вы с ним пусть и на очень упрощенном, но вполне понятном английском языке. Большинство слов совпадает. Подробнее о команде DROP будет рассказано позже, в главе 7, где рассмотрены действия с таблицами.
Можно ли было все это сделать одиночным запросом?
Да, но только используя совершенно не эффективный прием МАХ-СОNкошка, который приводится только в справочных целях:
SELECT article, SUBSTRING (МАХ(СОNкошка (LPAD (price, 6, 'O'), dealer)), 7)
AS dealer, 0.00+LEFT (MAX(СОNкошка(LPAD (price, 6, '0'),dealer)), 6)
AS price FROM shop GROUP BY article;
+---------+--------+-------+
| article | dealer | price |
+---------|--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------|--------+-------+
