sql server 交叉表查询实例－成绩统计

zgdy

　　首先我们建立如下的表：
　　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
　　其中stuid表示学生编号，clsid表示课程编号，scroe表示成绩
　　stu（学生表）
　　stuid       stuname
　　----------- --------------------------------------------------
　　101         张三
　　102         李四
　　103         王五
　　cls（课程表）
　　clsid       name
　　----------- --------------------------------------------------
　　1           语文
　　2           数学
　　3           英语
　　接下来就是关键了，其实也不算好复杂，就是用到了动态sql
　　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)