# 交叉表应用－成绩统计

sc（成绩表）
stuid clsid                                              scroe
----- -------- -----------------------------------------------------
0101  1                                                  75.0
0102  1                                                  70.0
0103  1                                                  90.0
0101  2                                                  89.0
0102  2                                                  80.0
0103  2                                                  99.0
0101  3                                                  89.0
0102  3                                                  79.0
0103  3                                                  67.0

stu（学生表）
stuid       stuname
----------- --------------------------------------------------
101         张三
102         李四
103         王五

cls（课程表）
clsid       name
----------- --------------------------------------------------
1           语文
2           数学
3           英语

declare @sql nvarchar(4000),@sql1 nvarchar(4000)
select @sql='',@sql1=''

select @sql=@sql+',['+name+']=sum(case clsid when '''+clsid+''' then scroe else 0 end)',
@sql1=@sql1+',['+name+'名次]=(select sum(1) from # where ['+name+']>=a.['+name+'])'
from(select distinct b.clsid,c.name from sc as b inner join cls as c on c.clsid=b.clsid) as a order by clsid

exec('select stuid 学号'+@sql+',总成绩=sum(scroe)
,平均分=Convert(dec(5,1),avg(scroe)),总名次=(select sum(1) from(select stuid,aa=sum(scroe) from sc group by stuid) aa where sum(a.scroe)<=aa) into # from sc as a group by stuid select b.stuname as 姓名,a.*'+@sql1+' from # as a inner join stu as b on a.学号=b.stuid')

---------------------------------------------------- --------------------------------------

• 本文已收录于以下专栏：

举报原因： 您举报文章：交叉表应用－成绩统计 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)