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

编辑：rootadmin

mysql> use test; Database changed mysql> CREATE TABLE shop ( -> article INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL, -> dealer CHAR() DEFAULT '' NOT NULL, -> price DOUBLE(,2) DEFAULT '0.' NOT NULL, -> PRIMARY KEY(article, dealer)); Query OK, 0 rows affected (0. sec) mysql> INSERT INTO shop VALUES -> (1,'A',3.),(1,'B',3.),(2,'A',.),(3,'B',1.), -> (3,'C',1.),(3,'D',1.),(4,'D',.); Query OK, 7 rows affected (0. sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select * from shop; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | | A | 3. | | | B | 3. | | | A | . | | | B | 1. | | | C | 1. | | | D | 1. | | | D | . | +---------+--------+-------+ 7 rows in set (0. sec) mysql> select article,max(price) from shop group by article -> ; +---------+------------+ | article | max(price) | +---------+------------+ | | 3. | | | . | | | 1. | | | . | +---------+------------+ 4 rows in set (0. sec) mysql> select article,max(price),dealer from shop group by article; +---------+------------+--------+ | article | max(price) | dealer | +---------+------------+--------+ | | 3. | A | | | . | A | | | 1. | B | | | . | D | +---------+------------+--------+ 4 rows in set (0. 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 | +---------+--------+-------+ | | B | 3. | | | A | . | | | C | 1. | | | D | . | +---------+--------+-------+ 4 rows in set (0. 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 | +---------+--------+-------+ | | B | 3. | | | A | . | | | C | 1. | | | D | . | +---------+--------+-------+ 4 rows in set (0. 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

推荐整理分享MySQL 查找价格最高的图书经销商的几种SQL语句(mysql 查找语句)，希望有所帮助，仅作参考，欢迎阅读内容。

文章相关热门搜索词:mysql查找数据库表中某一个值,mysql 查找语句,mysql查询价格最高,mysql查找最大值数据,mysql 查找某个字段最大值,mysql查询价格区间,mysql查询价格最高,mysql查询价格区间,内容如对您有帮助，希望把文章链接给更多的朋友！

MySQL 客户端不输入用户名和密码直接连接数据库的2个方法有2个方法一、可以修改my.ini配置参数(linux下面是my.cnf);[quote][client]port=default-character-set=utf8host=localhostuser=rootpassword=1[/quote]具体的其他参数都可以在这

mysql 查询表中平均分最低的班级 droptableifexistsdd;createtabledd(user_idint,class_noint,scoreint);insertintoddvalues(1,1,1),(2,1,1),(3,1,2),(4,2,2);selectclass_no,avg(score)fromddgroupbyclass_noorderbyavg(score);在MySQL下面测

mysql 左连接、右连接和内连接脚本如下：droptabletable1;CREATETABLE`andrew`.`table1`(`name`VARCHAR()NOTNULL,`city`VARCHAR()NOTNULL)ENGINE=MyISAM;insertintoTABLE1(name,city)values('PersonA','BJ');insertintoTABLE1(name,city