MAX函数和GROUP BY 语句一起使用的一个误区

部分转载地址：http://www.cnblogs.com/exmyth/p/3986680.html

使用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`)
         ) ENGINE=InnoDB DEFAULT CHARSET=gb2312                                       

select * from lkscore;

 

query result(12 records)

scoreid	chinese	math
1	90	80
2	100	99
3	29	98
4	87	79
5	89	99
1	49	98
3	98	56
2	76	88
2	80	90
3	90	70
1	90	90
1	67	90

错误的SELECT

select scoreid,chinese,max(math) max_math from lkscore group by scoreid;

 

query result(5 records)

scoreid	chinese	max_math
1	90	98
2	100	99
3	29	98
4	87	79
5	89	99

上面的90明显不对。
方法一：

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
1	49	98
2	100	99
3	29	98
4	87	79
5	89	99

这个是用MAX函数，而且还用到了相关子查询。

方法二比较复杂：

首先建立一个T表用来筛选每个人数学的最大值，会出现两个附属列，然后再从其中抽取所需要的部分：

select scoreid,chinese,math from (select * from lkscore as a, 
(select max(scoreid) as r_scoreid, max(math) 
as r_math from lkscore as b group by scoreid ) 
as b where a.scoreid=b.r_scoreid and a.math = b.r_math)T order by scoreid; 

这里多出来两列做对比

scoreid chinese math

1 49 98
2 100 99
3 29 98
4 87 79
5 89 99

此题经常出现在数据库和机器学习面试中，询问类似提出每个客户的最新一个订单记录。