请教2个问题 - 编程论坛

#2

ILoveMK2007-11-25 02:14

你的问题不难，我明天再给你答案吧，现在没空。

#3

ILoveMK2007-11-25 21:05

第一个问题：
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

#4

飙马2007-11-26 20:16

LS厉害!

#5

比蜗牛快些2007-12-04 21:59

厉害

" border="0" />