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