SQL查询学生的平均成绩及其名次

 

Student(Sid,Sname,Sage,Ssex) 学生表

Course(Cid,Cname,Tid) 课程表

SC(Sid,Cid,score) 成绩表

Teacher(Tid,Tname) 教师表 

SQL查询语句为：

SELECT 1+(SELECT COUNT( distinct 平均成绩)FROM (SELECT Sid,AVG(score) 平均成绩 FROM SC GROUP BY Sid ) T1 WHERE 平均成绩 > T2.平均成绩) 名次, Sid 学生学号,平均成绩

FROM (SELECT Sid,AVG(score) 平均成绩 FROM SC GROUP BY Sid ) T2

ORDER BY 平均成绩 desc;

分析：

1、SQL查询结果集为三列名次、学生学号、平均成绩；

2、ORDER BY 平均成绩 desc    语句定义了平均成绩从高到底排列，所以确定了学生学号、平均成绩两列的数据；

3、最复杂的就是给每个学生确定名次，语句：1+(SELECT COUNT( distinct 平均成绩)FROM (SELECT Sid,AVG(score) 平均成绩 FROM SC GROUP BY Sid ) T1 WHERE 平均成绩 > T2.平均成绩) 名次;

思路：首先第一行平均成绩最高的名次，比第一名高的有几个，结果自然是0个，但是名次是从1开始，所以需要加1；同理比第二名学生高的有一个，结果加1，所以第二名的名次为2，如果第二名学生的平均成绩和第一名学生的平均成绩一样，那比第二名学生高的也是0个，结果加1，所以第二名学生和第一名学生并列第一；

那么还有一个问题，名次是1，2，2，3的方式排序还是1，2，2，4的方式排序，这就是要不要加不加distinct了，加了就是1，2，2，3，不加就是1，2，2，4；

分析：加了distinct去重复值，假如第二名和第三名平均成绩相同，那么比第四名同学高的就只有两个，所以第四名同学的名次就是3（2+1）；不加distinct，那么比第四名同学高的有3个，所以第四名同学的名次就是4（3+1）；