自己已经解决给大家分享declare @sql varchar(8000)
set @sql = 'select name,count(name) as 总,sum() as 总分'
select @sql = @sql + ',count(case class when '''+class+''' then name end)['+class+'总数] ,sum(case class when '''+class+''' then end) ['+class+']'
from (select distinct class from temp) as sselect @sql = @sql+' from temp group by name'
--select @sql
exec(@sql)
set @sql = 'select name,count(name) as 总,sum() as 总分'
select @sql = @sql + ',count(case class when '''+class+''' then name end)['+class+'总数] ,sum(case class when '''+class+''' then end) ['+class+']'
from (select distinct class from temp) as sselect @sql = @sql+' from temp group by name'
--select @sql
exec(@sql)
select Name,count(1)'总数',
sum(Mark)'总分',
'总数学'=(select count(1) from #temp where name=a.name and class='数学'),
'总数学分'=(select sum(Mark) from #temp where name=a.name and class='数学'),
'总语文分'=(select count(1) from #temp where name=a.name and class='语文'),
'总语文'=(select sum(Mark) from #temp where name=a.name and class='语文')
from #temp a group by name