例61：查询学生的考试成绩（要求查询学号、姓名、课程名称和考试成绩），同时计算每个学生的平均成绩、最高成绩和最低成绩。

select 学生.学号,姓名,课程名称,考试成绩,平均成绩,最高成绩,最低成绩

from 选课,课程,学生,(select 选课.学号,avg(考试成绩) as 平均成绩,max(考试成绩)as 最高成绩,min(考试成绩)as 最低成绩 from 选课 group by 学号)as SC1

where 选课.课程编号=课程.课程编号 and 学生.学号=选课.学号 and SC1.学号=学生.学号;

--测试数据

if not object_id(N'Tempdb..#Student') is null

	drop table #Student

Go

Create table #Student([SNo] nvarchar(28),[Name] nvarchar(22))

Insert #Student

select N'04014101',N'曹波' union all

select N'04014111',N'成铭'

GO

if not object_id(N'Tempdb..#Course') is null

	drop table #Course

Go

Create table #Course([CNo] nvarchar(26),[Name] nvarchar(23))

Insert #Course

select N'B00016',N'数据库' union all

select N'B00101',N'运筹学' union all

select N'B00018',N'系统分析与设计'

GO

if not object_id(N'Tempdb..#Course_Student') is null

	drop table #Course_Student

Go

Create table #Course_Student([SNo] nvarchar(28),[CNo] nvarchar(26),[Scores] int)

Insert #Course_Student

select N'04014101',N'B00016',90 union all

select N'04014101',N'B00101',80 union all

select N'04014101',N'B00018',70 union all

select N'04014111',N'B00016',100 union all

select N'04014111',N'B00101',95 union all

select N'04014111',N'B00018',60

Go

--测试数据结束

SELECT  a.SNo ,

        b.Name ,

        c.Name ,

        a.Scores ,

        t.*

FROM    #Course_Student a

        JOIN #Student b ON b.SNo = a.SNo

        JOIN #Course c ON c.CNo = a.CNo

        JOIN ( SELECT   SNo ,

                        MAX(Scores) AS MaxScores ,

                        MIN(Scores) AS MinScores ,

                        AVG(Scores) AS AvgScores

               FROM     #Course_Student

               GROUP BY SNo

             ) t ON t.SNo = a.SNo