1.6.1 MAX的用法

查找最大的商品编号

mysql> select max(article) from shop;
+--------------+
| max(article) |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

查找最贵商品的编号、经销商和价格

mysql> SELECT article, dealer, price
    -> FROM shop
    -> WHERE price = (SELECT MAX(price) FROM shop);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+
1 row in set (0.00 sec)

该示例，也可以使用下面的方法是实现，但不推荐

mysql> 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 | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+
1 row in set (0.00 sec)

mysql> SELECT article, dealer, price
    -> FROM shop
    -> ORDER BY price DESC
    -> LIMIT 1;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+
1 row in set (0.00 sec)

Group 簇中的最大值

查找某一种商品的最高价格

mysql> SELECT article, MAX(price) AS price
    -> FROM shop
    -> GROUP BY article;
+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+
4 rows in set (0.00 sec)

对于每一种商品，找到最昂贵价格的经销商

先给出一个错误的查询示例

mysql> SELECT article, dealer, MAX(price) AS price
    -> FROM shop
    -> GROUP BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)

Note

如果您仔细，会发现在数据表 shop 中不存在 0001 | A | 3.99 这样的一条记录。为什么会出现这样的结果呢？这个根据 1.3.4.10 SELECT的执行顺序 ，该查询语句中先执行 GROUP BY 的操作，其次执行 SELECT 中 MAX 操作。

正确的操作方法应该是：

mysql> SELECT article, dealer, price
    -> FROM   shop s1
    -> WHERE  price=(SELECT MAX(s2.price)
    ->               FROM shop s2
    ->               WHERE s1.article = s2.article);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)

这里使用了相关子查询的操作，以后章节中，我们会单独介绍；这里我们可以先这么理解：先查找出相同的商品中的最大价格，然后根据这个价格查找出对应的信息。

我们也可以通过非相关子查询的方法来实现：

mysql> 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;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)

上面的例子就比较容易理解了；

Note

我们都知道，SELECT 查找在每一步都会产生一个虚拟表 VT ，在这个例子中，SELECT article, MAX(price) AS price FROM shop GROUP BY article; 产生一个虚拟表 VT，该表的结果与表 shop 合并，通过 ON 的条件筛选出最终结果。

还有一种实现：

mysql> 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;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)