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

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

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

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)

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)

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

(select StuID,count(score) from tb_score where Score > 80 group by StuID having count(score) = 4

select StuID,
count(
case
when score between 80 and 85 then score
else NULL
end
) bs from tb_score group by StuID

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

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

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

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

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

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

0
p

0
D

0

^_^其实类似的SQL挺多

0
Z

#### 引用来自“CheckStyle”的答案

0

0

0

`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为总的科目数量，可以为定值（假定大家都参加相同数量的考试），也可以实际求出（假定优秀学生评判标准只参考已参加的考试，而有学生未参加所有考试）。

0
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```

0

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

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

0