数据库

img jierry007

交叉表应用-成绩统计

发表于2004/9/30 15:31:00  1549人阅读

最近由于开发需要,涉及到了成绩统计这块,这里面设计到了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

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

阅读全文
0 0

相关文章推荐

img
取 消
img