MySQL 查找价格最高的图书经销商的几种SQL语句

mysql> use test;
database changed
mysql> create table shop (
-> article int(4) unsigned zerofill default '0000' not null,
-> dealer char(20) default '' not null,
-> price double(16,2) default '0.00' not null,
-> primary key(article, dealer));
query ok, 0 rows affected (0.13 sec)

mysql> insert into shop values
-> (1,'a',3.45),(1,'b',3.99),(2,'a',10.99),(3,'b',1.45),
-> (3,'c',1.69),(3,'d',1.25),(4,'d',19.95);
query ok, 7 rows affected (0.03 sec)
records: 7 duplicates: 0 warnings: 0

mysql> select * from shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | a | 3.45 |
| 0001 | b | 3.99 |
| 0002 | a | 10.99 |
| 0003 | b | 1.45 |
| 0003 | c | 1.69 |
| 0003 | d | 1.25 |
| 0004 | d | 19.95 |
+---------+--------+-------+
7 rows in set (0.06 sec)

mysql> select article,max(price) from shop group by article
-> ;
+---------+------------+
| article | max(price) |
+---------+------------+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
+---------+------------+
4 rows in set (0.05 sec)

mysql> select article,max(price),dealer from shop group by article;
+---------+------------+--------+
| article | max(price) | dealer |
+---------+------------+--------+
| 0001 | 3.99 | a |
| 0002 | 10.99 | a |
| 0003 | 1.69 | b |
| 0004 | 19.95 | d |
+---------+------------+--------+
4 rows in set (0.00 sec)

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.01 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.05 sec)

mysql> select s1.article,s1.dealer,s1.price from shop s1
-> left join shop s2 on s1.article=s2.article and s1.price select s1.article,s1.dealer,s1.price,s2.* from shop s1 left join shop s2
on s1.article=s2.article and s1.price