34,593
社区成员
发帖
与我相关
我的任务
分享
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