MySQL 查找价格最高的图书经销商的几种SQL语句
2022-11-12 09:19:32
内容摘要
这篇文章主要为大家详细介绍了MySQL 查找价格最高的图书经销商的几种SQL语句，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！mysql> use t
文章正文
这篇文章主要为大家详细介绍了MySQL 查找价格最高的图书经销商的几种SQL语句，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
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注：关于MySQL 查找价格最高的图书经销商的几种SQL语句的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
