MySQL 8.4 Release Notes
5.6.4 包含特定列分组最大值的行
任务:对每篇文章,找到最贵的经销商或经销商。
这个问题可以使用子查询来解决,如下所示:
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article)
ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+
前面的示例使用了相关子查询,可以不效率(见第15.2.15.7节,“相关子查询”)。其他解决问题的可能性是使用无关联子查询在FROM
子句中,一个LEFT JOIN
,或者使用通用表达式和窗口函数。
无关联子查询:
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price
ORDER BY article;
LEFT JOIN
:
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL
ORDER BY s1.article;
LEFT JOIN
基于s1.price
的最大值,不存在更大值的s2.price
,因此对应的s2.article
值为NULL
。见第15.2.13.2节,“连接子句”。
通用表达式和窗口函数:
WITH s1 AS (
SELECT article, dealer, price,
RANK() OVER (PARTITION BY article
ORDER BY price DESC
) AS `Rank`
FROM shop
)
SELECT article, dealer, price
FROM s1
WHERE `Rank` = 1
ORDER BY article;