--创建表
create table #A
(
xingming varchar(20),
kemu varchar(20),
timu int,
chengji float
)
insert #A
select '张三','语文',1, 10 union all
select '张三','语文',1,20 union all
select '张三','语文',2,10 union all
select '张三','数学',1,22 union all
select '李四','数学',3,2 union all
select '李四','数学',4,4 union all
select '张三','数学',5,10 union all
select '王五','英语',4,2 union all
select '王五','英语',3,5
--查询
select xingming as 姓名 ,kemu as 科目 ,sum(chengji) as 成绩
into #B
from #A group by xingming, kemu
select * from #B order by '姓名'
--释放
drop table #A
drop table #B