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

不同的图书，在不同的经销商的价格不同，我们这里要找到每种图书最高的经销商是谁？ 找最低的类似了。

 

mysql> use test;Database changedmysql> 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: 0mysql> 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 s2on s1.article=s2.article and s1.price