请教各位高手一个关于SQL的一个问题

shiyiming

我有一些数据，因分析的关系，所以想插入一些有特殊要求的ROW，如下
原来是这样的
id seq x
1 1 5
1 2 6
2 1 7
2 2 4
3 1 6
3 2 4
变幻后要求变成这样的（插入的行中，ID值不变，SEQ增一，X为前两个值的和）
id seq x
1 1 5
1 2 6
[color=red:d5534]1 3 11 [/color:d5534]
2 1 7
2 2 4
[color=red:d5534]2 3 11 [/color:d5534]
3 1 6
3 2 4
[color=red:d5534]3 3 10[/color:d5534]
希望高手不吝赐教，先行谢过

shiyiming

[code:1kqjdxuh]

data ads;
input id seq x;
cards;
1 1 5
1 2 6
2 1 7
2 2 4
3 1 6
3 2 4
;

proc sql noprint;
create table bds as
select id,sum(seq) as seq,sum(x) as x
from ads
group by id;
insert into ads (id,seq,x)
select *
from bds;
create table cds as
select *
from ads
order by id;
quit;

[/code:1kqjdxuh]

shiyiming

[code:1u0b1crz]--创建源表
drop table aa purge;
create table aa (id number, seq number,x number);
insert into aa values (1,1,5);
insert into aa values (1,2,6);
insert into aa values (2,1,7);
insert into aa values (2,2,4);
insert into aa values (3,1,6);
insert into aa values (3,2,4);
commit;
--创建变换表
drop table result purge;
create table result as
select * from(
select id,seq,x from aa
union all
select id,max(seq)+1,sum(x) from aa group by id
)
order by id,seq;
[/code:1u0b1crz]

shiyiming

mark!

shiyiming

proc sql;
create table b as
select id, sum(seq) as seq,sum(x) as x from a group by id;
select * from a union corr select * from b;
quit;