第一个问题：
SQL> select * from test;
COLA COLB COLC
---------- ---------- ----------
2000/03/01 50 30
2000/03/02 45 60
2000/03/05 60 10
2000/03/05 60 10
1.
select a,b,c,sum(d) over (order by a asc rows between unbounded preceding and current row) d from(
select cola a,sum(colb) b,sum(colc) c,sum(colb)-sum(colc) d from test
group by cola order by cola
) order by a
/
A B C D
---------- ---------- ---------- ----------
2000/03/01 50 30 20
2000/03/02 45 60 5
2000/03/05 120 20 105
2.
select a,b,c,sum(d) over (order by a rows between unbounded preceding and current row) d
from(
select cold a,nvl(sum(colb),0) b,nvl(sum(colc),0) c,nvl(sum(colb)-sum(colc),0) d
from test,
(select trunc(to_date('2000/03/01','yyyy/mm/dd'))+rownum-1 cold from dual
connect by rownum<(select to_number(to_char(max(cola),'dd'))+1 from test)) test2
where test2.cold=test.cola(+)
group by test2.cold
order by test2.cold
)
/
A B C D
---------- ---------- ---------- ----------
2000/03/01 50 30 20
2000/03/02 45 60 5
2000/03/03 0 0 5
2000/03/04 0 0 5
2000/03/05 120 20 105
第二个问题：
SQL> select * from test;
NAME NUM SAL
----- ---------- ----------
A 2 1000
B 3 2000
select nvl(name,'sum'),num,sum(sal) from test
group by rollup(name,num)
having grouping_id(name,num)!=1
/
NVL(N NUM SUM(SAL)
----- ---------- ----------
A 2 1000
B 3 2000
sum 3000
