13
回答
解一道SQL问题:找出成绩优秀的学生
利用AWS快速构建适用于生产的无服务器应用程序,免费试用12个月>>>   

问题:

“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做事了,这次一开始有明显的简单问题复杂化的嫌疑,不给力呀!面壁学习去。

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

文章转自:http://www.jeffkit.info/2011/03/944/

SQL
举报
红薯
发帖于6年前 13回/1K+阅
共有13个评论 最后回答: 6年前

不算复杂

俺说咱的需求吧, 一个大任务,有若干项子任务,分给N个人, 每个人分别Hold住若干个子任务.

大任务分配出去了, 大家开始做事,每个人做完分配给自己一个子任务,系统就会记录一下.

一句话SQL查出以下列:

任务, 多少个人被分配, 多少个子任务, 多少人全部完成,多少人部分完成,多少人全部未完成

--- 共有 2 条评论 ---
CheckStyle^_^其实类似的SQL挺多 6年前 回复
红薯新需求建议发新帖哦:) 6年前 回复

引用来自“CheckStyle”的答案

不算复杂

俺说咱的需求吧, 一个大任务,有若干项子任务,分给N个人, 每个人分别Hold住若干个子任务.

大任务分配出去了, 大家开始做事,每个人做完分配给自己一个子任务,系统就会记录一下.

一句话SQL查出以下列:

任务, 多少个人被分配, 多少个子任务, 多少人全部完成,多少人部分完成,多少人全部未完成

建议使用nested_set来做.

我觉得这个题目重点就是group by ,having以及case when,if的用法
虽然开始有in 和union,但后来几乎用不到了,
按效率排序,应该是in<where<having

整那么复杂干嘛(ma4)呀

SELECT stuid  FROM tbl GROUP BY stuid HAVING sum(score) >= (2*80 + (N-2)*85) 
SELECT stuid FROM tbl WHERE score >=80 GROUP BY stuid HAVING sum(score) >= (2*80 + (N-2)*85)

N为总的科目数量,可以为定值(假定大家都参加相同数量的考试),也可以实际求出(假定优秀学生评判标准只参考已参加的考试,而有学生未参加所有考试)。

 

 

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

既然是要做排除那么为啥不直接用上界判断而去做区间判断呢?

select StuID,count(*) from tb_score where Score < 85 group by StuID 
having count(*) >2

 

 

 

思路2中:

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

图中出现了Using filesort,而思路1的explain没有使用,如果是真实环境思路2只怕运行效率不会那么乐观的……

 这种循环测试没什么意思,数据不变化第一次以后就有缓存了。而真实环境下一般就只需要跑一次结果,如果再次跑也会是数据变动了。

 

顶部