交叉表应用－成绩统计
【聚杰网Asp】交叉表应用－成绩统计 最近由于开发需要，涉及到了成绩统计这块，这里面设计到了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
这就是交叉表的用法，对于报表统计很有用处，我们可以依此类推写出功能更强大的查询。
首先我们建立如下的表：
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
这就是交叉表的用法，对于报表统计很有用处，我们可以依此类推写出功能更强大的查询。
