统计成绩SQL语句

 

1.计算每个人的总成绩并排名
select CourseID,sum(Score) as allscore from ScoreInformation group by CourseID order by allscore

2.计算每个人的总成绩并排名
select distinct t1.StudentID,t2.allscore
from  ScoreInformation t1,(    select StudentID,sum(Score) as allscore from ScoreInformation group by StudentID)t2
where t1.StudentID=t2.StudentID
order by t2.allscore desc

3. 计算每个人单科的最高成绩
select t1.StudentID,t1.CourseID,t1.Score
from ScoreInformation t1,(select StudentID,max(Score) as maxscore
from ScoreInformation group by StudentID) t2
where t1.StudentID=t2.StudentID and t1.Score=t2.maxscore

4.计算每个人的平均成绩
select distinct t1.StudentID,t2.avgscore
from ScoreInformation t1,(select StudentID,avg(Score) as avgscore from ScoreInformation group by StudentID) t2
where t1.StudentID=t2.StudentID

5.列出各门课程成绩最好的学生
select  t1.StudentID,t1.CourseID,t2.maxscore
from ScoreInformation t1,(select CourseID,max(Score) as maxscore from ScoreInformation group by CourseID) t2
where t1.CourseID=t2.CourseID and t1.Score=t2.maxscore

6.学号     姓名     语文      数学      英语      总分   平均分
select StudentID as 学号,sum(case when CourseID='C#'
then Score else 0 end) as C#,sum(case when CourseID='ASP'
then Score else 0 end) as ASP,sum(case when CourseID='Computer'
then Score else 0 end) as Computer
,sum(Score) as 总分,(sum(Score)/count(*)) as 平均分
from ScoreInformation
group by StudentID
order by 总分 desc
 

7.列出各门课程的平均成绩
select CourseID,avg(Score) as avgscore from ScoreInformation group by CourseID

 

8. 课程     不及格（-59）    良（-80）    优（-100）
select CourseID,
(select count(*) from ScoreInformation where Score<60 and CourseID=t1.CourseID) as 不及格,
(select count(*) from ScoreInformation where Score between 60 and 80 and CourseID=t1.CourseID) as 良,
(select count(*) from ScoreInformation where Score >80 and CourseID=t1.CourseID) as 优
from ScoreInformation t1 group by CourseID

 

9、查询每门功成绩最好的前两名 
 SELECT t1.StudentID as 学生ID,t1.CourseID as 课程ID,Score as 分数 
      FROM ScoreInformation t1 
      WHERE Score IN (SELECT TOP 2 Score 
              FROM ScoreInformation 
              WHERE t1.CourseID= CourseID 
            ORDER BY Score DESC 
              ) 
      ORDER BY t1.CourseID; 

 

10、查询平均成绩大于60分的同学的学号和平均成绩； 
    select StudentID,avg(score) 
    from ScoreInformation
    group by StudentID having avg(Score) >60;

 

11、查询所有课程成绩小于60分的同学的学号、姓名； 
  select StudentID
  from StudentInformation 
  where StudentID not in
(select StudentID from ScoreInformation
where Score>60); 

 

12、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 
 SELECT L.CourseID As 课程ID,L.Score AS 最高分,R.Score AS 最低分 
    FROM ScoreInformation as L ,ScoreInformation AS R 
    WHERE L.CourseID = R.CourseID and 
        L.score = (SELECT MAX(IL.Score) 
                      FROM ScoreInformation AS IL
                      WHERE L.CourseID = IL.CourseID
                      GROUP BY IL.CourseID) 
        AND 
        R.Score = (SELECT MIN(IR.Score) 
                      FROM ScoreInformation AS IR 
                      WHERE R.CourseID= IR.CourseID 
                  GROUP BY IR.CourseID 
                    ); 

13、查询学生平均成绩及其名次 
      SELECT 1+(SELECT COUNT( distinct 平均成绩) 
              FROM (SELECT StudentID,AVG(Score) AS 平均成绩 
                      FROM ScoreInformation
                  GROUP BY StudentID
                  ) AS T1 
            WHERE 平均成绩 > T2.平均成绩) as 名次, 
      StudentID as 学生学号,平均成绩 
    FROM (SELECT StudentID,AVG(score) 平均成绩 
            FROM ScoreInformation
        GROUP BY StudentID 
        ) AS T2 
    ORDER BY 平均成绩 desc;