MySQL分组函数MAX，聚合函数GROUP BY与连接查询

先看看用于练习的表里面有些什么。

SELECT * FROM shop ORDER BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | A      |  3.45 |
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | B      |  1.45 |
|       3 | C      |  1.69 |
|       3 | D      |  1.25 |
|       4 | D      | 19.95 |
+---------+--------+-------+

 

• 查找价格(price)最贵的文章:

方法一：用聚合函数MAX求最大值

SELECT MAX(price) FROM shop;

 

方法二：用ORDER BY排序，用“LIMIT 1”只显示第一行

SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

 

方法三：用左连接

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;

 

• 找出每篇article的最高价格。

MAX函数求最大值，用GROUP BY给article分组。

聚合函数与分组函数一起使用时需注意，被GROUP BY分组的字段不可被聚合函数修饰。比如下面的article被GROUP BY了，article在SELECT时就不能被MAX().

SELECT article, MAX(price) AS price FROM   shop
GROUP BY article ORDER BY article;

 

聚合函数与GROUP BY一起使用有个弱点，如果我们要选择article,dealer,MAX(price),但仅以article分组计算最大值，这就不行。

注意：下面的SQL语句GROUP BY后面只有article。

SELECT article,dealer,MAX(price) AS price FROM shop
GROUP BY article ORDER BY article;

以下语句是正确的，但没有实现我们只按照article分组求最大值的目标。

SELECT article,dealer,MAX(price) AS price FROM shop
GROUP BY article,dealer ORDER BY article;

 

GROUP BY与聚合函数办不到的，就是连接查询大显神威的时刻的。

下面有请内连接出场。内连接是INNER JOIN，可以不写INNER。

同样的需求：对于每篇article，找出price最高的。

内连接

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;

 

参考连接

MySQL官网

https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html