一道著名的号称微软的试题的oracle解

 

TABLE如下
日期 收入 支出
2000/3/1 50 30
2000/3/2 45 60
2000/3/5 60 10

能否用SELECT語句得出以下結果

日期 收入 支出 余額
2000/3/1 50 30 20
2000/3/2 45 60 5
2000/3/3 0 0 5
2000/3/4 0 0 5
2000/3/5 60 10 55

这是一个网上广为流传的题目，以前曾经有人说这是微软的试题，这个不去管了，我们先看oracle的解

SQL> select * from test;

A B C
---------- ---------- ----------
1 1 0
2 2 1
4 4 2
6 1 3
8 4 4

已用时间: 00: 00: 00.00
SQL> select tt1.tta,tt1.ttb,tt1.ttc,sum(tt2.ttb) - sum(tt2.ttc) youwant
2 from
3 (
4 select t1.aa tta,decode(t2.a,null,0,t2.b) ttb,decode(t2.a,null,0,t2.c) ttc
5 from
6 (select ((select min(a) from test) + rownum - 1) aa from all_objects
7 where rownum <= (select max(a) - min(a) +1 from test)) t1,
8 test t2
9 where t1.aa = t2.a(+)
10 ) tt1,
11 (
12 select t1.aa tta,decode(t2.a,null,0,t2.b) ttb,decode(t2.a,null,0,t2.c) ttc
13 from
14 (select ((select min(a) from test) + rownum - 1) aa from all_objects
15 where rownum <= (select max(a) - min(a) +1 from test)) t1,
16 test t2
17 where t1.aa = t2.a(+)
18 ) tt2
19 where tt1.tta >= tt2.tta
20 group by tt1.tta,tt1.ttb,tt1.ttc;

TTA TTB TTC YOUWANT
---------- ---------- ---------- ----------
1 1 0 1
2 2 1 2
3 0 0 2
4 4 2 4
5 0 0 4
6 1 3 2
7 0 0 2
8 4 4 2

已选择8行。

已用时间: 00: 00: 00.00

 

816 以上版本使用analistic function 的简化版答案

SQL> select * from test;

A B C
---------- ---------- ----------
5 5 5
2 2 1
4 4 5
6 1 3
8 4 4

SQL> select tt2.tta, tt2.ttb, tt2.ttc, sum(tt2.ttb - tt2.ttc) over(order by tt2.tta) youwant
2 from
3 (
4 select t1.aa tta,decode(t2.a,null,0,t2.b) ttb,decode(t2.a,null,0,t2.c) ttc
5 from
6 (select ((select min(a) from test) + rownum - 1) aa from all_objects
7 where rownum <= (select max(a) - min(a) +1 from test)) t1,
8 test t2
9 where t1.aa = t2.a(+)
10 ) tt2;

TTA TTB TTC YOUWANT
---------- ---------- ---------- ----------
2 2 1 1
3 0 0 1
4 4 5 0
5 5 5 0
6 1 3 -2
7 0 0 -2
8 4 4 -2

已选择7行。

SQL>

 

        其实这类问题都是月度微通解的，主要就是构造连续的数字集合，如果all_objects记录数不足完全可以做个自连接。比如曾经有求某段时间内的工作日天数，这类问题，也是如此解法。