请教大家如何选取不在GROUP BY里的字段,使用一个SQL得出结果
例如以下这张表TABLE1
F1 F2 F3 C1
AAA 001 MODEL01 25
AAA 002 MODEL01 30
BBB 003 MODEL02 100
BBB 004 MODEL02 120我需要得出F1相同的C1的求和,并将其他字段一并输出。
结果:
->
F1 F2 F3 C1 SUM(C1)
AAA 001 MODEL01 25 55
AAA 002 MODEL01 30 55
BBB 003 MODEL02 100 220
BBB 004 MODEL02 120 220
请教大家这个SQL应该怎么写。
例如以下这张表TABLE1
F1 F2 F3 C1
AAA 001 MODEL01 25
AAA 002 MODEL01 30
BBB 003 MODEL02 100
BBB 004 MODEL02 120我需要得出F1相同的C1的求和,并将其他字段一并输出。
结果:
->
F1 F2 F3 C1 SUM(C1)
AAA 001 MODEL01 25 55
AAA 002 MODEL01 30 55
BBB 003 MODEL02 100 220
BBB 004 MODEL02 120 220
请教大家这个SQL应该怎么写。
select a.*,b.sumc1
from table1 as a
join (select f1,sum(c1) as sumc1 from table1 group by f1) as b
on a.f1=b.f1
SELECT F1,F2,F3,C1,SUM(C1) OVER(PARTITION BY F1) SUMC1
FROM TABLE1
from tb
from table1 a
2 from emp a
3 order by deptno
4 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO (SELECTSUM(B.SAL)FROMEMPBWHERE
--------------------- ---------- --------- ----- ----------- --------- --------- ------ ------------------------------
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 8750
7839 KING PRESIDENT 1981-11-17 5000.00 10 8750
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 8750
7369 SMITH CLERK 7902 1980-12-17 800.10 20 10875.1
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 10875.1
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 10875.1
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 10875.1
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 10875.1
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 9400
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 9400
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 9400
7900 JAMES CLERK 7698 1981-12-3 950.00 30 9400
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 9400
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 9400
14 rows selected
SQL>
insert into tc values('AAA', '001','MODEL01',25);
insert into tc values('AAA', '002','MODEL01', 30);
insert into tc values('BBB', '003', 'MODEL02', 100);
insert into tc values('BBB', '004', 'MODEL02', 120);select a.*,b.sumc1
from tc a
join (select f1,sum(nvl(c1,0)) sumc1 from tc group by f1) b
on a.f1=b.f1;SELECT F1,F2,F3,C1,SUM(nvl(C1,0)) OVER(PARTITION BY F1) SUMC1
FROM tc;select a.*,(select sum(nvl(c1,0)) from tc b where a.f1=b.f1) SUMC1
from tc a;
--都可以查出结果,第二种方法,还没领会其中真谛