Выберите несколько максимальных значений
У меня есть таблица с именем order , которая содержит столбцы id , user_id , price и item_id . Цены на предметы не являются фиксированными, и я хотел бы выбрать самый дорогой заказ для каждого предмета. Я хочу выбрать user_id , item_id и цену в одном запросе. Я попытался следующий запрос, но он не возвращает правильный набор результатов.
SELECT user_id, item_id, MAX(price)
FROM order
GROUP BY item_id
Некоторые строки, возвращаемые этим запросом, имеют неправильный user_id . Однако все строки в наборе результатов показывают правильную максимальную цену каждого элемента.
5 ответов
Вы можете использовать следующую производную таблицу:
SELECT o1.item_id, o1.max_price, o2.user_id user_of_max_price
FROM (
SELECT item_id, MAX(price) max_price
FROM `order`
GROUP BY item_id
) o1
JOIN `order` o2 ON (o2.price = o1.max_price AND o2.item_id = o1.item_id)
GROUP BY o1.item_id;
Тестовый пример:
CREATE TABLE `order` (user_id int, item_id int, price decimal(5,2));
INSERT INTO `order` VALUES (1, 1, 10);
INSERT INTO `order` VALUES (1, 2, 15);
INSERT INTO `order` VALUES (1, 3, 8);
INSERT INTO `order` VALUES (2, 1, 20);
INSERT INTO `order` VALUES (2, 2, 6);
INSERT INTO `order` VALUES (2, 3, 15);
INSERT INTO `order` VALUES (3, 1, 18);
INSERT INTO `order` VALUES (3, 2, 13);
INSERT INTO `order` VALUES (3, 3, 10);
Результат:
+---------+-----------+-------------------+
| item_id | max_price | user_of_max_price |
+---------+-----------+-------------------+
| 1 | 20.00 | 2 |
| 2 | 15.00 | 1 |
| 3 | 15.00 | 2 |
+---------+-----------+-------------------+
3 rows in set (0.00 sec)
Это максимальный вопрос для каждой группы. Существуют различные подходы к этой общей проблеме. В MySQL обычно быстрее и проще использовать нулевое самосоединение, чем что-либо, включающее подзапросы:
SELECT o0.user_id, o0.item_id, o0.price
FROM order AS o0
LEFT JOIN order AS o1 ON o1.item_id=o0.item_id AND o1.price>o0.price
WHERE o1.user_id IS NULL
т.е. «выбрать каждую строку, в которой нет другой строки для того же товара с более высокой ценой».
(Если две строки имеют одинаковую максимальную цену, вы получите обе. Что именно делать в случае ничьей, является общей проблемой для решений с максимальным значением для каждой группы.)
Ваш запрос группирует строки по item_id. Если у вас есть несколько элементов с item_id 1 с разными user_id, будет выбран только первый user_id, а не user_id с самая высокая цена.
Вам нужно сначала получить максимальную цену для каждого идентификатора товара, а затем присоединиться к заказу, чтобы получить записи о том, где товар был заказан по максимальной цене. Что-то вроде следующего запроса должно работать. Хотя он вернет все записи с максимальными ценами предметов.
SELECT user_id, item_id, price
FROM order o
JOIN (
SELECT item_id, max(price) max_price
FROM order
GROUP BY item_id
) o2
ON o.item_id = o2.item_id AND o.price = o2.max_price;
Вам нужно либо сгруппировать по item_id И user_id (показывая максимальную цену за товар для каждого пользователя), либо, если вам нужен только товар в группе, вам нужно переосмыслить столбец user_id. например показать максимальную цену предмета и показать ПОСЛЕДНЕГО пользователя, который изменил цену, ИЛИ показать максимальную цену предмета и показать пользователя, который СДЕЛАЛ максимальную цену предмета и т. д. Взгляните на это post для некоторых шаблонов для этого.