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 |
+---------+-----------+----------+-------+
同样将在SELECT
语句MIN
做:
+---------+-----------+----------+-------+ | NAME | MAX_PRICE | DATE | BRAND |
+---------+-----------+----------+-------+
| apples | 1.95 | 28/11/17 | Aldi |
| bananas | 0.5 | 01/12/17 | Tesco |
+---------+-----------+----------+-------+
怎么可以这样写在MySQL
?
回答:
我没有我自己尝试,但如果你将添加到最大的日期,并在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 ;
回答:
MySQL有非标准的功能,使这些查询比标准SQL更简单:
SELECT name AS NAME,
MAX(products.price) AS MAX_PRICE, date, brand
FROM products
GROUP BY name;
它会随机地从要获取日期和品牌的子集中选择一个元组。 如果从{姓名,价格}到{日期,品牌} 存在功能依赖关系,则这相当于将日期和品牌添加到群组。
回答:
这样做没有group by
。这里有一种方法:
select p.* from products p
where p.price = (select max(p2.price) from products p2 where p2.name = p.name);
这不仅是正确的,但它可以在products(name, price)
采取指数的优势。
回答:
您只需要连接结果来获取结果中的其他行。
SELECT p.name AS NAME,p.price AS MAX_PRICE, p.date as DATE, p.brand as BRAND FROM products p JOIN
(
SELECT brand, max(price) as brand_max_price
FROM products p
GROUP BY brand
) t
ON p.brand = t.brand AND p.price = t.brand_max_price;
回答:
您可以通过再次向同一个表添加连接来重用自己的查询。
SELECT Z.NAME, Z.MAX_PRICE, A.DATE, A.BRAND FROM
products A
INNER JOIN
(SELECT products.name AS NAME, MAX(products.price) AS MAX_PRICE
FROM products
GROUP BY products.name) Z
ON A.NAME = Z.NAME AND A.price = Z.MAX_PRICE;
以上是 MYSQL - 按名称分组,并包含结果中其他列的相应值 的全部内容, 来源链接: utcz.com/qa/257648.html