这个Group怎么写呀

Question

数据是这样的学号   科目   成绩
------------------------------------------------
001   A1     20
001   A2     30
001   A3     40
001   B1     20
001   Z1     5
002   A1     20
002   B1     30
002   Z1     5
003   B1     40
003   Z1     5想把Z1的成绩加到A系列成绩最高的那条记录上，然后如果没有A系列成绩的话，加到B1上
想要的结果是
学号   科目   成绩
------------------------------------------------
001   A1     20
001   A2     30
001   A3     45
001   B1     20
001   Z1     5
002   A1     25
002   B1     30
002   Z1     5
003   B1     45
003   Z1     5SQL应该怎么写呀，谢谢了

Answer 1

create table tabletest_1
(
Sno varchar(10),
Cno varchar(10),
Score int
)
delete from tabletest_1
insert into tabletest_1 values ('001','A1' ,'20');
insert into tabletest_1 values ('001','A2' ,'30');
insert into tabletest_1 values ('001','A3' ,'40')
insert into tabletest_1 values ('001','B1' ,'20')
insert into tabletest_1 values ('001','Z1' ,'5')
insert into tabletest_1 values ('002','A1' ,'20')
insert into tabletest_1 values ('002','B1' ,'30')
insert into tabletest_1 values ('002','Z1' ,'5')
insert into tabletest_1 values ('003','B1' ,'40')
insert into tabletest_1 values ('003','Z1' ,'5')--想把Z1的成绩加到A系列成绩最高的那条记录上，然后如果没有A系列成绩的话，加到B1上
--想要的结果是
select Sno,Cno,score,
case when exists (select MAX(Cno) from tabletest_1 I1 where I1.Sno=O.Sno and Cno like 'A%')
then 
(select MAX(Score) from tabletest_1 I2 where I2.Sno=O.Sno and Cno like 'A%')
+
(select Score from tabletest_1 I2 where I2.Sno=O.Sno and Cno = 'Z1' ) 
else
(select MAX(Score) from tabletest_1 I2 where I2.Sno=O.Sno and Cno = 'B1')
+
(select Score from tabletest_1 I2 where I2.Sno=O.Sno and Cno = 'Z1' ) 
end as score2
from tabletest_1 O

Answer 2

create table #tb(学号 varchar(10),科目 varchar(10),成绩 int)
insert into #tb
select '001','A1',20
union all select '001','A2',30
union all select '001','A3',40
union all select '001','B1',20
union all select '001','Z1',5
union all select '002','A1',20
union all select '002','B1',30
union all select '002','Z1',5
union all select '003','B1',40
union all select '003','Z1',5select a.学号,a.科目,成绩=a.成绩+isnull((select 成绩 from #tb t where c.学号=t.学号 and c.成绩=t.成绩  ),0)
from #tb a
left join (select 学号,max(成绩) as 成绩 from #tb group by 学号)b
on a.学号=b.学号 and a.成绩=b.成绩
left join (select * from #tb where 科目='Z1')c
on b.学号=c.学号 
drop table #tb/*
学号   科目   成绩
-------------------------
001 A1 20
001 A2 30
001 A3 45
001 B1 20
001 Z1 5
002 A1 20
002 B1 35
002 Z1 5
003 B1 45
003 Z1 5*/