mysql - MYSQL-按名称分组，并在结果中包括其他列的相应值

首先，我想对标题产生误解表示歉意。

我在mysql中有一个名为产品的表：

+---------+-------+-----------+-------+
| name    | price | date      | brand |
+---------+-------+-----------+-------+
| apples  | 2     | 02/12/17  | Tesco |
| apples  | 1.95  | 28/11/17  | Aldi  |
| apples  | 2.5   | 29/11/17  | Lidl  |
| bananas | 0.5   | 01/12/17  | Tesco |
| bananas | 0.7   | 29/11/17  | Aldi  |
| bananas | 1     | 25/11/17  | Lidl  |
+---------+-------+-----------+-------+

如果我希望从此表中SELECT价格MAX，我将继续执行以下查询：

SELECT
  products.name AS NAME,
  MAX(products.price) AS MAX_PRICE
FROM products
GROUP BY products.name;

哪个会输出：

+---------+-----------+
| NAME    | MAX_PRICE |
+---------+-----------+
| apples  | 2.5       |
| bananas | 1         |
+---------+-----------+

但是，我也想在查询输出中包括相应的日期和品牌，如下所示：

+---------+-----------+----------+-------+
| NAME    | MAX_PRICE | DATE     | BRAND |
+---------+-----------+----------+-------+
| apples  | 2.5       | 29/11/17 | Lidl  |
| bananas | 1         | 25/11/17 | Lidl  |
+---------+-----------+----------+-------+

MIN语句中的SELECT也会这样做：

+---------+-----------+----------+-------+
| NAME    | MAX_PRICE | DATE     | BRAND |
+---------+-----------+----------+-------+
| apples  | 1.95      | 28/11/17 | Aldi  |
| bananas | 0.5       | 01/12/17 | Tesco |
+---------+-----------+----------+-------+

如何用MySQL编写？

最佳答案

我没有亲自尝试过，但是如果您将max添加到日期，并将其添加到group子句中，例如：

SELECT
  products.name AS NAME,
  MAX(products.price),MAX(products.date)  AS MAX_date,BRAND
FROM products

GROUP BY products.name,products.date,BRAND ;