交叉表应用－成绩统计

新客网 XKER.COM 2004-10-09 来源： 收藏本文

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

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

• ·ASP+ACCESS数据库中文乱码问题解决
• ·编程实例 字母+数字验证码程序
• ·ASP连接数据库错误解决办法新法
• ·初学:ASP内建对象Response
• ·ASP网站Server object error的解决办法
• ·ASP技巧：让Len,Left,Right函数识别中文
• ·把网页中的电话号码生成图片的ASP程序
• ·ASP如何实现IE地址栏参数的判断
• ·细化解析：ASP连接11种数据库语法集锦
• ·ASP实现网页打开任何类型文件都保存
• ·ASP代码直接增加、删除ACCESS表和字段
• ·ASP：判断访问是否来自搜索引擎的函数
• ·ASP技巧：禁用页面缓存的五种方法
• ·学习ASP关于与变量子类型相关的函数
• ·ASP开发中的(VBScript)类基础学习
• ·ASP访问Excel文件