27,579
社区成员
发帖
与我相关
我的任务
分享
declare @s varchar(8000)
declare @where varchar(100)
select @s = isnull(@s+',','') + '[CourseID('+ltrim(CourseID)+')]=max(case when CourseID = '+
ltrim(CourseID) + ' then Score else 0 end)'
from (select distinct CourseID from Scores ) a
exec(' select studentID,'+@s+',总分=sum(Score) ,平均分= avg(Score),名次=identity(int,1,1) into tmp from (select * from Scores where'+ @where +')a group by studentID order by sum(Score) desc')
select * from tmp
drop table tmp