[font=Arial]TABLE[/font]如下

日期
收入
支出

[font=Arial]2000/3/1 50 30[/font]
[font=Arial]2000/3/2 45 60[/font]
[font=Arial]2000/3/5 60 10[/font]
[font=Arial]2000/3/5 60 10 [/font]
能否用[font=Arial]SELECT[/font]語句得出以下結果

[font=Arial]1.[/font]
日期
收入
支出
餘額

[font=Arial]2000/3/1 50 30 20[/font]
[font=Arial]2000/3/2 45 60 5[/font]
[font=Arial]2000/3/5 120 20 105[/font]
[font=Arial]2.[/font]
日期
收入
支出
餘額

[font=Arial]2000/3/1 50 30 20[/font]
[font=Arial]2000/3/2 45 60 5[/font]
[font=Arial]2000/3/3 0 0 5[/font]
[font=Arial]2000/3/4 0 0 5 [/font]




[font=Arial][font=Arial][size=1][font=Arial][size=9pt]1.[/font]用[font=Arial]SQL Plus[/font]產生以下報表[font=Arial].(scott/tiger@mdstest)[/font]


[font=Arial]   1.[/font]使用表[font=Arial]emp,dept[/font]
[font=Arial]   2.[/font]寫成一個可以用在[font=Arial]SQL Plus[/font]中執行的[font=Arial]Script[/font]

使用[font=Arial]SQLPlus[/font]的[font=Arial]column[/font]等定義

[font=Arial]   3. Format[/font]



部門名稱
人數
工資總額



[font=Arial]        A                       2             1000[/font]
[font=Arial]        B                       3             2000[/font]
[font=Arial]        sum                                   3000[/font]
[font=Arial]最后一行SUM怎么加？[/font]
[/size][/font][/size][/font]

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

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

LS厉害!

厉害" border="0" />
" border="0" />