use StudentManager

 go

 if exists(select * from sysobjects where name='usp_ScoreQuery')

 drop procedure usp_ScoreQuery

 go

 create procedure usp_ScoreQuery --创建存储过程

 as

     --查询考试信息

     select Students.StudentId,StudentName,ClassName,

               ScoreSum=(CSharp+SQLServerDB) from Students

     inner join StudentClass on StudentClass.ClassId=Students.ClassId

     inner join ScoreList on Students.StudentId=ScoreList.StudentId

     order by ScoreSum DESC

     --统计分析考试信息

     select StudentClass.ClassId,C#Avg=avg(CSharp),DBAvg=avg(SQLServerDB)  into #scoreTemp

     from StudentClass

     inner join Students on StudentClass.ClassId=Students.ClassId

     inner join ScoreList on ScoreList.StudentId=Students.StudentId

     group by StudentClass.ClassId order by ClassId

     select ClassName,C#Avg,DBAvg from #scoreTemp

     inner join StudentClass on StudentClass.ClassId=#scoreTemp.ClassId

 go

 exec usp_ScoreQuery  --调用存储过程