1、select a.*,b.最底分,b.StudentID,b.StudentName,b.TeacherID,b.TeacherName from (
select classid,classname,score 最高分,StudentID, StudentName, TeacherID,TeacherName from SctTable tem where score=(select max(score) from scttable where classid=tem.classid)) a
,(
select classid,classname,score 最底分,StudentID, StudentName, TeacherID,TeacherName from SctTable tem where score=(select min(score) from scttable where classid=tem.classid)) b
where a.classid=b.classid
select classid,classname,score 最高分,StudentID, StudentName, TeacherID,TeacherName from SctTable tem where score=(select max(score) from scttable where classid=tem.classid)) a
,(
select classid,classname,score 最底分,StudentID, StudentName, TeacherID,TeacherName from SctTable tem where score=(select min(score) from scttable where classid=tem.classid)) b
where a.classid=b.classid
select StudentID,StudentName
,sum(case when classname='数学' then score else 0 end) 数学
,sum(case when classname='语文' then score else 0 end) 语文
,sum(case when classname='英语' then score else 0 end) 英语
,sum(case when classname='政治' then score else 0 end) 政治
,count(score) 有效课程数
,avg(score) 平均分数
from SctTable
group by StudentID,StudentName order by avg(score) desc
set @sql = 'select StudentID,StudentName'
select @sql = @sql + ',sum(case ClassID when '''+cast(ClassID as varchar(10))+''' then score else 0 end) ['+ClassName+']'
from (select distinct ClassID,ClassName from SctTable) as a
select @sql = @sql+',count(score) 有效课程数,avg(score) 平均分数 from SctTable group by StudentID,StudentName order by avg(score) desc'exec(@sql)
go