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

分类 数据库
------------------------------------------------------------

首先我们建立如下的表：

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

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