交叉表应用－成绩统计

最近由于开发需要，涉及到了成绩统计这块，这里面设计到了sql交叉表的应用，试了试，觉得很有感想，现在写出来大家看看，有什么不对的欢迎指正^_^

首先我们建立如下的表：

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)<=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'')

以下就是结果：

姓名 学号 语文 数学 英语 总成绩 平均分 总名次 语文名次 数学名次 英语名次
---------------------------------------------------- --------------------------------------
张三 0101 75.0 89.0 89.0 253.0 84.3 2 2 2 1
李四 0102 70.0 80.0 79.0 229.0 76.3 3 3 3 2
王五 0103 90.0 99.0 67.0 256.0 85.3 1 1 1 3

这就是交叉表的用法，对于报表统计很有用处，我们可以依此类推写出功能更强大的查询。