注:表结构来自mysql中文参考手册
表结构和表数据如下
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));
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); 使用shell脚本构造数据 #! /bin/bash for ((i=10009;i<=1000000;i++)); do `mysql -uroot -proot b -e "insert into shop value ($i,'A',3.45);"`; done 手册中给出这么一道题: 对每项物品,找出最贵价格的物品的经销商。 答案: 1)SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article); 我看了好久,想不清楚上面sql的逻辑。于是按照自己的思路鼓捣一条sql,如下: 2)SELECT article, dealer, price FROM shop s1 WHERE price in (select max(price) from shop group by article); 还有一条是一个师弟给出的: 3)select article,(select s2.dealer from shop s2 where s2.price = max(s1.price) limit 1) from shop s1 group by article; 三者的执行计划如下: mysql> explain SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article); +----+--------------------+-------+------+---------------+---------+---------+--------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+---------+---------+--------------+--------+-------------+ | 1 | PRIMARY | s1 | ALL | NULL | NULL | NULL | NULL | 125935 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | ref | PRIMARY | PRIMARY | 4 | b.s1.article | 1 | | +----+--------------------+-------+------+---------------+---------+---------+--------------+--------+-------------+ 2 rows in set (0.00 sec) mysql> explain select article,(select s2.dealer from shop s2 where s2.price = max(s1.price) limit 1) from shop s1 group by article; +----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+ | 1 | PRIMARY | s1 | index | NULL | PRIMARY | 24 | NULL | 125935 | | | 2 | DEPENDENT SUBQUERY | s2 | ALL | NULL | NULL | NULL | NULL | 125935 | Using where | +----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+ 2 rows in set (0.00 sec) mysql> explain SELECT article, dealer, price FROM shop s1 WHERE price in (select max(price) from shop group by article); +----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+ | 1 | PRIMARY | s1 | ALL | NULL | NULL | NULL | NULL | 125935 | Using where | | 2 | DEPENDENT SUBQUERY | shop | index | NULL | PRIMARY | 24 | NULL | 1 | | +----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+ 2 rows in set (0.00 sec)
从执行计划中可以看出,1)性能最好,3)遍历了整张表两次,2)比1)性能稍差,但是逻辑比较好理解