用一句SQL语句查询成绩优良中差的人数

遇到一个基础的笔试题，有关sql查询的，感觉很有趣，正好复习下相关的知识。

成绩表如下：

1.查询出每门课程的成绩都大于80的学生姓名

-- 通过分组聚合函数，最小的分数都大于80
SELECT sname
FROM test.Score
group by sname 
having min(snum)>80
;
-- 通过all 关键字,all要放到比较的右侧
select sname
from test.Score t1
where 80<all(select snum from test.Score t2 where t2.sname=t1.sname);
-- 逆向思维
select sname
from test.Score t1
where not exists(select 1 from test.Score t2 where t2.sname=t1.sname and t2.snum<=80);

2.用一句SQL语句查询成绩优良中差的人数

规定：
<60 不合格 
>=60 <70 中
>=70 <90  良
>=90 优

SELECT 
sum(case when snum<60 then 1 else 0 end) AS 不合格,
sum(case when snum>=60 AND snum<70 then 1 else 0 end) AS 中,
sum(case when snum>=70 AND snum< 90 then 1 else 0 end) AS 良,
sum(case when snum>=90 then 1 else 0 end) AS 优
FROM test.Score;

3. 按姓名分组，查看每个学生的优良中差统计

SELECT sname, count(snum) 总数,
sum(case when snum<60 then 1 else 0 end) AS 不合格,
sum(case when snum>=60 AND snum<70 then 1 else 0 end) AS 中,
sum(case when snum>=70 AND snum< 90 then 1 else 0 end) AS 良,
sum(case when snum>=90 then 1 else 0 end) AS 优
FROM test.Score
group by sname
;

4. 按科目分组，查看优良中差

SELECT clazz, count(snum) 总数,
sum(case when snum<60 then 1 else 0 end) AS 不合格,
sum(case when snum>=60 AND snum<70 then 1 else 0 end) AS 中,
sum(case when snum>=70 AND snum< 90 then 1 else 0 end) AS 良,
sum(case when snum>=90 then 1 else 0 end) AS 优
FROM test.Score
group by clazz
;

点击下载源码