select xh, sum(xf) as zxf,t1.xz1,t2.xz2,t3.xz3,t4.xz4 from (select xh,sum(xf) as xz1 from v_cjb where kcxz='1' group by xh) t1,(select xh,sum(xf) as xz2 from v_cjb where kcxz='2' group by xh) t2,(select xh,sum(xf) as xz3 from v_cjb where kcxz='3' group by xh) t3,(select xh,sum(xf) as xz4 from cjb where kcxz='4' group by xh) t4 group by xh 这是我的解决方案,可是好像行不通
declare @ls_colname varchar(10)
declare @ls_update varchar(500)
select @li_max = max(课程性质) from 成绩表
set @li_i = 0
while @li_i < @Li_max
begin
set @li_i = @li_i + 1
set @ls_colname = cast(@li_i as varchar(10))
exec('alter table #temp add 课程性质' + @ls_colname + '总学分 varchar(50) ')
select @ls_update = 'update a set 课程性质' + @ls_colname + '总学分 = sum(b.学分) from #temp a, 成绩表 b where a.学号 = b.学号 and b.课程性质 = ' + @ls_colname
exec(@ls_update)
endselect * from #tempdrop table #temp
select id,xid,sum(f),(select sum(f) from table3 where id=a.id) from table3 a group by id, xid order by id
1 1 1 2
1 2 1 1
1 3 3 2
2 1 1 2
2 2 1 1
2 3 3 2
2 4 4 2学号 课程性质 学分 总学分
1 1 3 5
1 3 2 5
2 1 3 7
2 3 2 7
2 4 2 7
insert 成绩表
select '01' , 'k1' , 1 , 2 union all
select '01' , 'k2' , 1 , 1 union all
select '01' , 'k3' , 3 , 2 union all
select '02' , 'k1' , 1 , 2 union all
select '02' , 'k2' , 2 , 1 union all
select '02' , 'k3' , 3 , 2 union all
select '02' , 'k4' , 4 , 2/*学号 课程性质1总学分 课程性质2总学分 课程性质3总学分 综合总学分 */declare @sql varchar(8000)
set @sql='select 学号 '
select @sql=@sql+',课程性质'+cast(课程性质 as varchar(5))+'总得分=sum(case 课程性质 when '+
cast(课程性质 as varchar(5))+' then 学分 else 0 end )' from 成绩表
group by 课程性质 order by 课程性质set @sql=@sql+',综合总学分=sum(学分) from 成绩表 group by 学号 order by 学号'
exec(@sql)
insert @tb
select '01' ,'k1',1 ,2 union all
select '01' ,'k1',1 ,1 union all
select '01' ,'k3',3 ,2 union all
select '02' ,'k1',1 ,2 union all
select '02' ,'k2',2 ,1 union all
select '02' ,'k3',3 ,2 union all
select '02' ,'k4',4 ,2 /*学号 课程性质1总学分 课程性质2总学分 课程性质3总学分 综合总学分 */select a.学号,
isnull((select sum(学分) from @tb where 学号=a.学号 and 课程性质=1),0)'课程性质1总学分',
isnull((select sum(学分) from @tb where 学号=a.学号 and 课程性质=2),0)'课程性质2总学分',
isnull((select sum(学分) from @tb where 学号=a.学号 and 课程性质=3),0)'课程性质3总学分',
isnull((select sum(学分) from @tb where 学号=a.学号),0)综合总学分
from @tb a group by 学号
--结果:
学号 课程性质1总学分 课程性质2总学分 课程性质3总学分 综合总学分
01 3 0 2 5
02 2 1 2 7