The Rows Holding the Group-wise Maximum of a Certain Column

任务：对于每个article，找出最贵价格的经销商

可以用一个子查询来解决此问题

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 |
+---------+--------+-------+

上面例子使用了一个关联子查询，这是很低效的(查询Section 13.2.10.7 "Correlated Subqueries")。

解决此问题的其他可能是使用不相关联的子查询(FROM 或 LEFT JOIN)

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 |
+---------+--------+-------+

LEFT JOIN

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.price is null;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0002 | A      | 10.99 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)