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

TABLE如下

2000/3/1 50 30
2000/3/2 45 60
2000/3/5 60 10

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

SQL> select * from test;

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

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

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

SQL>

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

• 本文已收录于以下专栏：

举报原因： 您举报文章：一道著名的号称微软的试题的oracle解 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)