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

来源：互联网发布：97719网络口语的意思编辑：程序博客网时间：2024/04/29 01:50

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

成绩表如下：

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

-- 通过分组聚合函数，最小的分数都大于80SELECT snameFROM test.Scoregroup by sname having min(snum)>80;-- 通过all 关键字,all要放到比较的右侧select snamefrom test.Score t1where 80<all(select snum from test.Score t2 where t2.sname=t1.sname);-- 逆向思维select snamefrom test.Score t1where 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.Scoregroup 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.Scoregroup by clazz;

点击下载源码

阅读全文

0 0