Jeff的妙想奇境

今天中午，珠三角技术沙龙官方群（103903642）的Crazy同学给大家出了一道SQL的题目，据说来自某个微群：

“SQL开发的一道小问题，一个学校的老师需要评选一组学生作为优秀学生，条件为最多只有2个科目在80-85之间，其他科目在85分以上或者所有科目成绩都在85分以上，表中包含StuId，SubjectId，Score，求最简单且效率最高的语句。 ”

我有点无聊加手欠，就试着解了一下，建立测试用数据表及数据如下，一共有5位同学，4个科目，共20条数据。我的测试环境是老式macbook 402，4G内存，mysql 5.1.42，MyISAM引擎。

我们首先把非优秀学生的条件重新整理一下：

1、凡有一科分数低于80分的，都不能称为优秀学生
2、凡有两科以上分数在80至85分之间的，也不能称为优秀学生
这两个条件是或的关系。

同时整理一下优秀学生的条件：

1、称得上优秀学生的，必须至少所有科目都在80以上。
2、称得上优秀学生的，分数在80至85分的科目必须小于2科。
这两个条件是与的关系。

先找出非优秀学生还是先找出优秀学生，是两种不同的解题思路，下面我分别从这两种思路出发解决问题：

思路一：先找出非优秀的学生，反过来得到优秀的学生

1、首先找出有个别科目分数低于80分的学生
select StuID,count(*) from tb_score where Score < 80 group by StuID

2、然后找出有科目分数在80至85分超过两科的学生
select StuID,count(*) from tb_score where Score between 80 and 85 group by StuID
having count(*) >2

而未出现在结果集1及2中的学生，则可视为是优秀学生，把上面两句整合到一起，最后得到一句完整的SQL：

select distinct(StuID) from tb_score where StuId not in
(select StuID from tb_score where Score < 80 group by StuID
union
select StuID from tb_score where Score between 80 and 85 group by StuID having Count(score) > 2)

执行结果得出优秀学生为：vera、Selina。

效率测试结果：反复执行10次，最快2.1ms,最慢3.7ms，平均执行时间2.42ms, explain 一下，该SQL对表进行了3次查询，每次都是全表扫描：

实际上，这条SQL语句可以稍优化一下，先distinct出StuID的结果集，把该结果集去与子查询的结果进行not in操作，会比把所有包括重复的StuID直接进行not in操作效率高不少。对原SQL做了一下调整：

select dst.StuID from (select distinct (StuID) from tb_score) dst where StuId not in
(select StuID from tb_score where Score < 80 group by StuID
union
select StuID from tb_score where Score between 80 and 85 group by StuID having Count(score) > 2)

再次测试得到相同的结果，效率测试的结果：反复执行10次，最快1.2ms，最慢1.7ms，平均执行时间1.36ms,较原SQL有44%的提升！再explain一下，新语句用1，2两步来代替旧语句的步骤1，实际上多执行了一次查询。但是这两次查询的代价开销加起来比原语句的远远要小。

根据思路一给出结果后，King兄说使用in的效率还是不理想。好吧，我再试一试第二种思路。

思路二：直接找出成绩优秀的学生

即，要先找出全部科目分数均在80分以上的学生
(select StuID,count(score) from tb_score where Score > 80 group by StuID having count(score) = 4

注意，这里HardCode了一个值4，这是所有科目的总数，这也是本方法的一个瑕疵。

然后计算所有学生分数在80-85分之间的科目总数
select StuID,
count(
case
when score between 80 and 85 then score
else NULL
end
) bs from tb_score group by StuID

最后把他们放在一起，由于两者是与的关系，我使用innner join来表达：

select A.StuID from
(select StuID,count(score) from tb_score where Score > 80 group by StuID having count(score) = 4) as A
inner join
(select StuID,
count(
case
when score between 80 and 85 then score
else NULL
end
) bs from tb_score group by StuID) as B
on A.StuID = B.StuID where B.bs <= 2

测试结果与思路一的结果一致，执行效率测试结果：反复执行10次，最快0.8ms,最慢1.0ms，平均执行速度为0.81ms，再次获得40%以上的性能提升。explain结果如下，该语句减少了一次对物理表（tb_score）的查询，是查询性能提升的直接原因。

思路三：再简单点！再高效点！

经caoxg同学提醒，应该可以再减少一次查询得出结果，我回想一下思路二，其实再往前走一步就可以变成只查询一次了。遂花了两分钟再次码出新版SQL：

select stuID from
(select stuID,count(case when score < 80 then score else null end ) as low_count,
count(case when score between 80 and 85 then score else null end ) as median_count
from tb_score group by stuID)tmp
where tmp.low_count = 0 and tmp.median_count <=2

这一次的平均执行效率在0.8ms以下。再explain一下，结果好看多了。

嗯，事情还没完呢。caoxg同学不满足于where从句那里有两个条件，好吧，我说，条件是可以转移的，使用having从句在子查询里过滤掉分数为80分以下的人就可以把tmp.low_count=0这个条件去掉，相当于把条件提前了，不过这样一来会影响主查询语句的查询基数，理论上是可以带来一定程度的提升，下面是修改过后的语句：

select stuID from
(select stuID,count( case when score between 80 and 85 then score else null end) as median_count from tb_score group by stuID
having count(case when score < 80 then score else null end) = 0) tmp
where tmp.median_count <=2

这一次平均执行效率再次获得0.1ms的提升，正如前面说的，主查询语句的查询基数由5变成了3,见explain的结果：

嗯，现在看起来很不错了，如果说这已经是最简单的查询就错了，King胸在群里回复说：@jeff，我在你的语句基础上回复了一下，用一句SQL搞定。

看了King给的版本，是把select从句中的Count再次合并到having从句中，最终变成了只需要select一次，同时，把Case从句换成IF函数，这样语句看起来更短了：

select StuId from tb_score group by StuId
having count(if(score < 80, score, NULL)) = 0
and count(if(score between 80 and 85, score, NULL)) <= 2

执行效率与上面的语句差不多，但精简程度是更进一步了。explain一下，结果只有一次查询了。帅！

目前效率最高的查询语句还是有having从句，having从句在解决复杂问题时非常有用，常与DB打交道的同学一定不陌生的，having即相当于针对聚合函数的where从句啊。

ps. 挺长时间没动手用纯SQL做事了，这次一开始有明显的简单问题复杂化的嫌疑，不给力呀！面壁学习去。

如果还有更简单高效的方法，请一定要提醒俺 ：）