任务:对每项物品,找出最贵价格的物品的经销商。
可以用这样一个子查询解决该问题:
SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.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)