MAX函数和GROUPBY语句⼀起使⽤的⼀个误区
使⽤MAX 函数和 GROUP 的时候会有不可预料的数据被SELECT 出来。
下⾯举个简单的例⼦：
想知道每个SCOREID 的数学成绩最⾼的分数。
表信息：
/*DDL Information For - test.lkscore*/
--------------------------------------
Table    Create Table
-------  -----------------------------------------------------------------------------
lkscore  CREATE TABLE `lkscore` (
`scoreid` int(11) DEFAULT NULL,
`chinese` int(11) DEFAULT '0',
`math` int(11) DEFAULT '0',
KEY `fk_class` (`scoreid`),
CONSTRAINT `fk_class` FOREIGN KEY (`scoreid`) REFERENCES `lkclass` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
select * from lkscore;
query result(12 record
scoreid chinese math
19080
210099
32998
48779
58999
14998
39856
27688
28090
39070
19090
16790
错误的SELECT
select scoreid,chinese,max(math) max_math from lkscore group by scoreid;
query result(5 records)
scoreid chinese max_math
19098
210099
32998
48779
58999
上⾯的90明显不对。
⽅法⼀：
select scoreid,chinese,math max_math from
(
select * from lkscore order by math desc
) T
group by scoreid;
query result(5 records)
scoreid chinese max_math
14998
210099
32998
48779
58999
⽅法⼆：
select * from lkscore a where a.math = (select max(math) from lkscore where scoreid = a.scoreid) order by scoreid asc;
query result(5 records)
scoreid chinese max_math
14998
210099
32998
48779
58999
这个也是⽤MAX函数，⽽且还⽤到了相关⼦查询。
我们来看⼀下这两个的效率如何：
explain
select scoreid,chinese,math max_math from (select * from lkscore order by math desc) T group by scoreid;
query result(2 records)
id select_type table type possible_keys key key_len ref rows Extra
1PRIMARY<derived2>ALL(NULL)(NULL)(NULL)(NULL)12Using temporary; Using filesort
2DERIVED lkscore ALL(NULL)(NULL)(NULL)(NULL)12Using filesort
很明显，有两个FULL TABLE SCAN。
explain
groupby是什么函数select scoreid,chinese,math max_math from lkscore a where a.math =
(select max(math) from lkscore where scoreid = a.scoreid) order by scoreid asc;
(select max(math) from lkscore where scoreid = a.scoreid) order by scoreid asc;
query result(2 records)
id select_type table type possible_keys key key_len ref rows Extra
1PRIMARY a index(NULL)fk_class5(NULL)12Using where 2DEPENDENT SUBQUERY lkscore ref fk_class fk_class5  a.scoreid1Using where
第⼆个就⽤了KEY,⼦查询⾥只扫描了⼀跳记录。
很明显。在这种情况下第⼆个⽐第⼀个效率⾼点。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。