如下建表后
sc（成绩表）
stuid clsid
scroe
----- -------- -----------------------------------------------------
01011
75.0
01021
70.0
01031
90.0
01012
89.0
01022
80.0
01032
99.0
01013
89.0
01023
79.0
01033
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')
以下就是结果：
姓名学号语文数学英语总成绩平均分 总名次 语文名次 数学名次 英语名次
---------------------------------------------------- --------------------------------------
张三 010175.089.089.0 253.084.3 2
2
2
1
李四 010270.080.079.0 229.076.3 3
3
3
2
王五 010390.099.067.0 256.085.3 1
1
1
3
把中间那段转成一句sql =.= |
