MySQL queries several SQL statements of the most expensive book dealers _ MySQL

MySQL queries several SQL statements of the most expensive book dealers bitsCN. commysql> use test;
Database changed
Mysql> create table shop (
-> Article INT (4) unsigned zerofill default '100' 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 |
| 1, 0003 | C | 1.69 |
| 1, 0003 | D | 1.25 |
| 1, 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 |
| 1, 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 |
| 1, 0003 | C | 1.69 |
| 1, 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 |
| 1, 0003 | C | 1.69 |
| 1, 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.pricebitsCN.com