这个Group怎么写呀
数据是这样的
学号 科目 成绩
------------------------------------------------
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 5
SQL应该怎么写呀，谢谢了
[解决办法]
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
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',5
select 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
/*
学号 科目 成绩
-------------------------
001A120
001A230
001A345
001B120
001Z15
002A120
002B135
002Z15
003B145
003Z15
*/
