请用下面的语句创建测试数据
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中
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中
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
---------- ---------- ----------
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>
(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