7-25.sql

来自「SQL2000跟课件学习的源代码 分享共同学习」· SQL 代码 · 共 12 行

USE Educational
GO
SELECT SC.Term 学期, C.ClassID 班号, S.StudentName 姓名, AVG(Grade) 平均分
FROM Grade G
  INNER JOIN Student S  ON  S.StudentID = G.StudentID
  INNER JOIN Class C ON C.ClassID = S.ClassID
  INNER JOIN Speciality_Course SC ON C.SpecialityID = SC.SpecialityID
        and SC.CourseID=G.CourseID
  INNER JOIN Course Co ON Co.CourseID = G.CourseID
GROUP BY  SC.Term, C.ClassID, S.StudentName
ORDER BY  SC.Term, C.ClassID, 平均分 DESC