1 use StudentManager
2 go
3 if exists(select * from sysobjects where name='usp_ScoreQuery')
4 drop procedure usp_ScoreQuery
5 go
6 create procedure usp_ScoreQuery --创建存储过程
7 as
8 --查询考试信息
9 select Students.StudentId,StudentName,ClassName,
10 ScoreSum=(CSharp+SQLServerDB) from Students
11 inner join StudentClass on StudentClass.ClassId=Students.ClassId
12 inner join ScoreList on Students.StudentId=ScoreList.StudentId
13 order by ScoreSum DESC
14 --统计分析考试信息
15 select StudentClass.ClassId,C#Avg=avg(CSharp),DBAvg=avg(SQLServerDB) into #scoreTemp
16 from StudentClass
17 inner join Students on StudentClass.ClassId=Students.ClassId
18 inner join ScoreList on ScoreList.StudentId=Students.StudentId
19 group by StudentClass.ClassId order by ClassId
20 select ClassName,C#Avg,DBAvg from #scoreTemp
21 inner join StudentClass on StudentClass.ClassId=#scoreTemp.ClassId
22 go
23 exec usp_ScoreQuery --调用存储过程
这里的 into #scoreTemp 是把查询的数据放入到临时表中
