学生各门课程成绩统计SQL语句大全(笔试题)

笔试的时候是看一张成绩表，包括姓名，课程，成绩，学号。三个问题，第一，查询某科成绩大于80分的学生。二，学生成绩统计并排名。三，展示一个表格，增加一列，展示每一个学生低于60分就算不及格的，高于60算及格。

具体题目忘记了，从网上找了个相似的练习下。工作上用的比较多的是查询，其它的毕竟好几年没复习了，早就忘了。

成绩表数据如下：

一、查询每个人的总成绩并按从高到低排名(要求显示字段：姓名，总成绩，学号) 

考察聚合函数sum()，用于返回数值列的总数（总额）。。group by 分组（配合聚合函数使用），排序用order by，默认从小到大，加个desc，从大到小。没有where子句查询所有记录

select name,SUM(score),stid from dbo.stuscore
group by name,stid 
order by SUM(score) desc;

运行结果（一块复制进来了）（Navicat命令列界面）

mysql> select name,SUM(score),stid from dbo.stuscore
group by name,stid 
order by SUM(score) desc;
+------+------------+------+
| name | SUM(score) | stid |
+------+------------+------+
| 张三 | 218        |    1 |
| 李四 | 200        |    2 |
| 王五 | 191        |    3 |
+------+------------+------+
3 rows in set (0.03 sec)

二、查询每个人单科最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)

考察派生表（需要别名），max()函数，用于返回一列中的最大值，NULL 值不包括在计算中。先查学号与最高成绩，再把学号，姓名，课程，最高成绩查出来

select table1.stid,table1.name,table1.subject,table1.score from stuscore table1,
(select stid,max(score) as maxscore from stuscore group by stid) table2 
where table1.stid=table2.stid and table1.score=table2.maxscore;

先查括号里面的

mysql> select stid,max(score) as maxscore from stuscore group by stid;
+------+----------+
| stid | maxscore |
+------+----------+
|    1 |       89 |
|    2 |       80 |
|    3 |       91 |
+------+----------+
3 rows in set (0.03 sec)

结果，再把相关信息列出来

mysql> select table1.stid,table1.name,table1.subject,table1.score from stuscore table1,
(select stid,max(score) as maxscore from stuscore group by stid) table2 
where table1.stid=table2.stid and table1.score=table2.maxscore;
+------+------+---------+-------+
| stid | name | subject | score |
+------+------+---------+-------+
|    1 | 张三 | 数学    |    89 |
|    2 | 李四 | 英语    |    80 |
|    3 | 王五 | 数学    |    91 |
+------+------+---------+-------+
3 rows in set (0.04 sec)

三、查询每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）

考察avg()函数，用于返回数值列的平均值，NULL 值不包括在计算中。和分组group by用法

select name,stid,avg(score) from dbo.stuscore
group by name,stid;

运行结果

mysql> select name,stid,avg(score) from dbo.stuscore
group by name,stid;
+------+------+------------+
| name | stid | avg(score) |
+------+------+------------+
| 张三 |    1 | 72.6667    |
| 李四 |    2 | 66.6667    |
| 王五 |    3 | 63.6667    |
+------+------+------------+
3 rows in set (0.04 sec)

四、查询各门课程成绩最好的学生(要求显示字段: 学号，姓名，科目，成绩) 

类似第二题。第一步查询把学号 换成科目

select table1.stid,table1.name,table1.subject,table1.score from stuscore table1,
(select subject,max(score) as maxscore from stuscore group by subject) table2 
where table1.subject=table2.subject and table1.score=table2.maxscore;

运行结果

mysql> select table1.stid,table1.name,table1.subject,table1.score from stuscore table1,
(select subject,max(score) as maxscore from stuscore group by subject) table2 
where table1.subject=table2.subject and table1.score=table2.maxscore;
+------+------+---------+-------+
| stid | name | subject | score |
+------+------+---------+-------+
|    2 | 李四 | 语文    |    70 |
|    2 | 李四 | 英语    |    80 |
|    3 | 王五 | 数学    |    91 |
+------+------+---------+-------+
3 rows in set (0.04 sec)

五、查询各门课程成绩最好的两位学生(要求显示字段: 学号，姓名，科目，成绩) 

考察top子句，用于规定要返回的记录的数目。