准备的表与数据:
mysql> desc shop ;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| article | varchar(20) | YES | | NULL | |
| dealer | char(1) | YES | | NULL | |
| price | double | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
mysql> select * from shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | D | 20.5 |
| 0002 | C | 11.5 |
| 0003 | F | 81.5 |
+---------+--------+-------+
方式1:
mysql> select s1.* from shop s1 left join shop s2 on s1.price <s2.price where s2.article is null;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0003 | F | 81.5 |
+---------+--------+-------+
方式2:
mysql> select * from shop s1 where price = (select max(price) from shop s2);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0003 | F | 81.5 |
+---------+--------+-------+
方式3:
mysql> select * from shop order by price desc limit 1;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0003 | F | 81.5 |
+---------+--------+-------+