[求助]挑战你的SQL，双倍给分(200分)

[amengdewo]

请用下面的语句创建测试数据
create table test(ID int, F1 number, F2 number);
insert into test values(1, 1, 1);
insert into test values(2, 1, 1);
insert into test values(3, 1, 1);
insert into test values(4, 1, 1);
insert into test values(5, 1, 1);
insert into test values(6, 2, 2);
insert into test values(7, 2, 2);
insert into test values(8, 2, 2);
insert into test values(9, 2, 2);
insert into test values(10, 2, 2);
insert into test values(11, 3, 3);
insert into test values(12, 3, 3);
insert into test values(13, 3, 3);

请用一个SQL语句得到下面的形式
ID F1 F2 SUMF1 SUMF2
-- ---------- ------------------------------
1 1 1 5 5
2 1 1 5 5
3 1 1 5 5
4 1 1 5 5
5 1 1 5 5
6 2 2 10 10
7 2 2 10 10
8 2 2 10 10
9 2 2 10 10
10 2 2 10 10
11 3 3 9 9
12 3 3 9 9
13 3 3 9 9


每5行对F1,F2分别求和，分别放在SUMF1,SUMF2中

[zjcxc]

--SQL Server与oracle的语法差别有多大?

select a.*,b.SUMF1,c.SUMF2
from test a
join(
select f1,SUMF1=sum(f1)
from test
group by f1
)b on a.f1=b.f1
join(
select f2,SUMF2=sum(f2)
from test
group by f2
)c on a.f2=b.f2

[bzszp]

SQL> select * from test;

ID F1 F2
---------- ---------- ----------
1 1 1
2 1 1
3 1 1
4 1 1
5 1 1
6 2 2
7 2 2
8 2 2
9 2 2
10 2 2
11 3 3

ID F1 F2
---------- ---------- ----------
12 3 3
13 3 3

已选择13行。

SQL> select id,f1,f2,sumf1,sumf2 from test,(
2 select trunc((id-1)/5) tid,sum(f1) sumf1,sum(f2) sumf2 from test group by trunc((id-1)/5)
3 ) t
4 where trunc((id-1)/5)=t.tid(+);

ID F1 F2 SUMF1 SUMF2
---------- ---------- ---------- ---------- ----------
1 1 1 5 5
2 1 1 5 5
3 1 1 5 5
4 1 1 5 5
5 1 1 5 5
6 2 2 10 10
7 2 2 10 10
8 2 2 10 10
9 2 2 10 10
10 2 2 10 10
11 3 3 9 9

ID F1 F2 SUMF1 SUMF2
---------- ---------- ---------- ---------- ----------
12 3 3 9 9
13 3 3 9 9

已选择13行。

SQL>

[amengdewo]

谢谢两位， bzszp(www.bzszp.533.net) 的方法是可以的

[jxc]

select f1,f2 ,
(select sum(f1) from test t2 where t1.f1 = t2.f1),
(select sum(f2) from test t2 where t1.f1 = t2.f1)
from test t1