部分转载地址: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
此题经常出现在数据库和机器学习面试中,询问类似提出每个客户的最新一个订单记录。