解一道SQL问题:找出成绩优秀的学生

今天中午,珠三角技术沙龙官方群(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做事了,这次一开始有明显的简单问题复杂化的嫌疑,不给力呀!面壁学习去。

如果还有更简单高效的方法,请一定要提醒俺 :)

This entry was posted in 技术 and tagged , . Bookmark the permalink.

18 Responses to 解一道SQL问题:找出成绩优秀的学生

  1. adam.lu says:

    暂时没有更好的方案:(

    请问截图中的软件是什么?

  2. caoxg says:

    考虑把分数按条件转变为-1,0,1,分别对应85, 然后按人头sum,再where一次解决。花了一分钟的直觉,应该可以在不超过两次全表查询解决。

    • jeff says:

      的确如此,不到五分钟,第三种方案出来了:
      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,
      count(
      case
      when score > 85 then score
      else null
      end
      ) as high_count
      from tb_score
      group by stuID) tmp
      where tmp.low_count = 0 and tmp.median_count <=2

      只执行一次全表查询。速度比其他的都快。谢谢提醒!

  3. caoxg says:

    计算count的那部分比较繁琐,应该还有优化的空间。最好让where只用一个布尔判断。

    • jeff says:

      我去掉了hign_count的count查询,这个是多余的。
      如果让where只有一个判断条件,那Count或Sum部份则会变得更复杂。有时间再试试 :)

    • jeff says:

      例如可以这样,只留下一个布尔判断:
      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

  4. King says:

    貌似可以一句搞定
    SELECT StuId
    FROM `tb_score`
    GROUP BY StuId
    HAVING COUNT(CASE WHEN score < 80 THEN score ELSE NULL END) = 0
    AND COUNT(CASE WHEN score BETWEEN 80 AND 85 THEN score ELSE NULL END) <= 2

  5. King says:

    貌似也可以用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;

  6. tivan says:

    在King基础上,
    加一个假设 如果科目是固定4科的话,而且数据量大的时候。
    用下面改造过的语句更快。

    select StuId from test_sd
    where score > 80
    group by StuId
    having count(subjectid) = 4
    and count(case when (score between 80 and 85) then score else NULL end) <= 2;

    • tivan says:

      刚刚把Oracle的写法弄上去了
      我改:
      SELECT StuId
      FROM `tb_score`
      WHERE score > 80
      GROUP BY StuId
      HAVING COUNT(subjectid) = 4
      AND COUNT(IF(score BETWEEN 80 AND 85, score, NULL)) <= 2;

  7. sun says:

    想了解一下那个签到系统的应用,,请指教

  8. mark says:

    SELECT stuid FROM tbl WHERE score >=80 GROUP BY stuid HAVING sum(score) >= (2*80 + (N-2)*85)
    N为总的科目数量,可以为定值(假定大家都参加相同数量的考试),也可以实际求出(假定优秀学生评判标准只参考已参加的考试,而有学生未参加所有考试)。

    http://www.oschina.net/question/12_26391

  9. alypai.com says:

    写得真不错,小MM很喜欢哦,随便说一下留言是一种美德,你是知道的!多谢支持!http://www.alypai.com

  10. yobune.com says:

    博主写得不错,真有才!我要支持你,看了你的博客,好象我和你生活在不同的世界一样,呵呵.我每天每天都在工作工作,你呢~在那边还好吧

发布评论

您的电子邮箱不会被公开。 标记为 * 的区域必须填写

*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>