SELECT * FROM shop s1 WHERE price = (SELECT MAX(price) FROM shop s2 WHERE s1.article=s2.article);

3.6.4. 拥有某个字段的组间最大值的行

任务：对每项物品，找出最贵价格的物品的经销商。

可以用这样一个子查询解决该问题：

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

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

mysql> SELECT * FROM shop s1 WHERE price = (SELECT MAX(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> explain SELECT * FROM shop s1 WHERE price = (SELECT MAX(price) FROM shop s2 WHERE s1.article=s2.article);
+----+--------------------+-------+------+---------------+---------+---------+---------------------+------+-------------+
| id | select_type        | table | type | possible_keys | key     | key_len | ref                 | rows | Extra       |
+----+--------------------+-------+------+---------------+---------+---------+---------------------+------+-------------+
|  1 | PRIMARY            | s1    | ALL  | NULL          | NULL    | NULL    | NULL                |    7 | Using where |
|  2 | DEPENDENT SUBQUERY | s2    | ref  | PRIMARY       | PRIMARY | 4       | duba_nav.s1.article |    1 | NULL        |
+----+--------------------+-------+------+---------------+---------+---------+---------------------+------+-------------+
2 rows in set (0.00 sec)

mysql> explain SELECT article ,dealer, MAX(price) FROM shop GROUP BY article;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | shop  | index | PRIMARY       | PRIMARY | 24      | NULL |    7 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)