sql 大挑战-- 可以放到精华区的几个命题.txt

oracle问题集

SQL 大挑战-- 可以放到精华区的几个命题

这里有三道题，对于第三题，考虑了两天，还没有最让我满意的解法--我想能够自动补充日期间隔，但是没有办法只用select语句实现，要借助于PL/SQL才能动态生成，大家都来动动脑吧。

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

能否用SELECT語句得出以下結果
1.
日期 收入 支出 余額
2000/3/1 50 30 20
2000/3/2 45 60 5
2000/3/5 60 10 55
2.
TABLE加入一行
2000/3/5 60 10
結果
日期 收入 支出 余額
2000/3/1 50 30 20
2000/3/2 45 60 5
2000/3/5 120 20 105
3.
日期 收入 支出 余額
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 120 20 105

__________________
hold a minute, Let ME THINK..


--------------------------------------------------------------------------------
由 biti_rainy 于 02-07-29 22:44 发表: 
这个题目，不就是一个构造么，呵呵

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
SQL>
__________________
I love oracle
But i hate IT


--------------------------------------------------------------------------------
由 biti_rainy 于 02-07-29 22:46 发表: 
你直接把a换成日期类型

一样的效果
__________________
I love oracle
But i hate IT


--------------------------------------------------------------------------------
由 haitian 于 02-07-29 23:11 发表: 

SQL> SELECT a.a,a.b,a.c ,
2 sum(decode(least(a.a,b.a),b.a,b.b - b.c,0)) ye
3 from test a,test b
4 group by a.a,a.b,a.c
5 
SQL> /

A B C YE
---------- --------- --------- ---------
07-7月 -02 50 30 20
23-7月 -02 45 60 5
25-7月 -02 60 10 55


--------------------------------------------------------------------------------
由 darkstorm 于 02-07-31 21:17 发表: 
利用all_objects来获得连续的number，想法不错

关键的一句在于
select ((select min(a) from test) + rownum - 1) aa from all_objects
where rownum <= (select max(a) - min(a) +1 from test)

给了我一个很好的思路，谢谢
__________________
hold a minute, Let ME THINK..


--------------------------------------------------------------------------------
由 darkstorm 于 02-07-31 21:57 发表: 
自动日期增长和填补

利用biti_rainy的方法，可以实现日期的增长。如下：
select * from test；
A B C RQ
---------- ---------- ---------- ----------
1 1 0 31-7? -02
2 2 1 01-8? -02
4 4 2 02-8? -02
6 1 3 03-8? -02
8 4 4 04-8? -02
20 1 0 15-8? -02

select ((select min(rq) from test) + rownum - 1) rq from all_objects
where rownum <= (select max(rq) - min(rq) +1 from test);

RQ
----------
31-7? -02
01-8? -02
02-8? -02
03-8? -02
04-8? -02
05-8? -02
06-8? -02
07-8? -02
08-8? -02
09-8? -02
10-8? -02

RQ
----------
11-8? -02
12-8? -02
13-8? -02
14-8? -02
15-8? -02

已选择16行

有了这个方法，我的问题基本的一解决了，多谢网友的帮助
__________________
hold a minute, Let ME THINK..


--------------------------------------------------------------------------------
由 victorora 于 02-08-01 23:38 发表: 

各位高手们呀，请问，可不可以仔细一点说明这句的意思呀，
select ((select min(rq) from abc) + rownum - 1) rq from all_objects
where rownum <= (select max(rq) - min(rq) +1 from abc)
为什么我总是执行不出来呀，为什么呢？请帮忙解答！


--------------------------------------------------------------------------------
由 biti_rainy 于 02-08-02 00:47 发表: 
这个意思很明显呀

到底是出什么错误呢？

816 以上版本的简化版答案 

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>
__________________
I love oracle
But i hate IT


--------------------------------------------------------------------------------
由 victorora 于 02-08-02 16:37 发表: 

首先，我公司用的是ORACLE7。3。4，还有错误提示为MISSING EXPRESSION
它提示的错误行在（select min(rq) from abc），但我单执行这一句就可以，还有我的RQ用的是VARCHAR2值，可以吗？

是不是7。3。4不可用呀


--------------------------------------------------------------------------------
由 victorora 于 02-08-02 16:46 发表: 

select ((select min(rq) from abc) + rownum - 1) rq from all_objects
where rownum <= (select max(rq) - min(rq) +1 from abc)

up;

desc abc 
rq varcahr2(8)


--------------------------------------------------------------------------------
由 biti_rainy 于 02-08-02 17:51 发表: 
734？

这个我说不好了
差异太大
__________________
I love oracle
But i hate IT


--------------------------------------------------------------------------------
由 hahaer 于 02-08-02 17:55 发表: 

I will try the new analistic functions avaiable in 8i. You can easily translate a column from number to date.

[e-mail]>create table t
2 ( a number not null,
3 income number,
4 expense number
5 ) pctfree 0 nologging
6 /

Table created.

[e-mail]> 
1 insert into t
2 select trunc(dbms_random.value(1,20)), round(dbms_random.value(100,10000),2),round(dbms_random.value(100,10000),2)
3 from all_objects
4* where rownum <= 10
5 /

10 rows created.

[e-mail]>commit;

Commit complete.

[e-mail]>select * from t;

A INCOME EXPENSE
---------- ---------- ----------
12 1010.29 1747.35
17 7495.49 1892.23
3 3129.15 9190.91
10 7148.18 336.46
15 1490.9 459.25
10 7852.9 8493.48
2 8115.83 2149.76
8 7962.83 3464.67
9 3178.35 8983.54
5 2737.83 3524.53

10 rows selected.
[e-mail]> 
1 select a,sum(income) income, sum(expense) expense ,balance
2 from (
3 select a, income,expense,income - expense,sum(gap) over (order by a rows between unbounded preceding and current row) balance
4 from
5 ( select a,income,expense, income-expense as gap from t
6 union all
7 select minval + rownum -1 as a, 0 income, 0 expense, 0 gap
8 from
9 all_objects t1,
10 ( select min(a) minval,max(a) maxval
11 from t ) t2
12 where rownum <= maxval - minval - 1
13 )
14* ) group by a,balance
[e-mail]>/

A INCOME EXPENSE BALANCE
---------- ---------- ---------- ----------
2 8115.83 2149.76 5966.07
3 3129.15 9190.91 -95.69
4 0 0 -95.69
5 2737.83 3524.53 -882.39
6 0 0 -882.39
7 0 0 -882.39
8 7962.83 3464.67 3615.77
9 3178.35 8983.54 -2189.42
10 7852.9 8493.48 3981.72
10 7148.18 336.46 4622.3
11 0 0 3981.72
12 1010.29 1747.35 3244.66
13 0 0 3244.66
14 0 0 3244.66
15 1490.9 459.25 4276.31
17 7495.49 1892.23 9879.57

16 rows selected.

And I am not sure wether it is ok to code with all_objects included, since the record of all_objects may run out comparing to the requirement from table a. I use all_objects a lot in test eviroment, but I try to conquer it in production. Any coments are welcome.
__________________
If not me, who? If not now, when?
------------------
MSN:[e-mail]


--------------------------------------------------------------------------------
由 biti_rainy 于 02-08-02 18:25 发表: 
rows between unbounded preceding and current row

hahaer

我觉得 expert one on one 上关于分析函数讲的最好 

不过上面的这个是缺省的，所以我就没有写了，我觉得写了更让人迷糊
__________________
I love oracle
But i hate IT


--------------------------------------------------------------------------------
由 hahaer 于 02-08-02 22:07 发表: 

呵呵，你也有EXPORT ONE TO ONE。真是哥们
__________________
If not me, who? If not now, when?
------------------
MSN:[e-mail]


--------------------------------------------------------------------------------
由 biti_rainy 于 02-08-02 22:13 发表: 
你这个logo太……

不如 overtime 的哪个扭的好看 

BTW: 给你发了个 PM
__________________
I love oracle
But i hate IT


--------------------------------------------------------------------------------
由 darkstorm 于 02-08-03 01:48 发表: 
整理个答案给大家

根据biti_rainy的all_objects（他那个logo总让我想入非非）的方法，我会整理出一个答案给大家参考。

希望还有其它方法，因为一旦是要处理好几万天的数据，all_objects就不够用了---
不过那时为此写个动态生成的sql就可以了。不必要非要用select
__________________
hold a minute, Let ME THINK..


--------------------------------------------------------------------------------
由 biti_rainy 于 02-08-03 01:57 发表: 
因为一旦是要处理好几万天的数据

select ... from all_objects,all_objects where rownum < ?
这样足够了 
__________________
I love oracle
But i hate IT


--------------------------------------------------------------------------------
由 darkstorm 于 02-08-03 17:02 发表: 
我的答案

为方便阅读故，复杂的SQL被写成view
一、建立一个表：
SQL> desc test2
名称 是否为空? 类型
----------------------------------------- -------- -----------
RQ DATE
INCOME NUMBER(9)
OUTCOME NUMBER(9

二、插入三笔数据
RQ INCOME OUTCOME
---------- ---------- ----------
01-3月 -00 50 30
02-3月 -00 45 60
05-3月 -00 60 10

三、第一个答案
create view result1(seq,rq,income,outcome,balance) as
select t1.row1,t1.rq,t1.income,t1.outcome,sum(t2.dif2) from
(select rownum row1,rq,income,outcome,income-outcome dif1 from test2) t1,
(select rownum row2,rq,income-outcome dif2 from test2) t2
where t1.row1 >= t2.row2
group by t1.row1,t1.rq,t1.income,t1.outcome

这个view就是答案

四、第二个答案
首先，插入一笔数据
RQ INCOME OUTCOME
---------- ---------- ----------
05-3月 -00 60 10

然后
create view result2(rq,income,outcome,balance) as
select t3.rq,sum(t3.income),sum(t3.outcome),max(dif3) from
(
select t1.row1,t1.rq,t1.income,t1.outcome,sum(t2.dif2) dif3 from
(select rownum row1,rq,income,outcome,income-outcome dif1 from test2) t1,
(select rownum row2,rq,income-outcome dif2 from test2) t2
where t1.row1 >= t2.row2
group by t1.row1,t1.rq,t1.income,t1.outcome
) t3
group by t3.rq

就是答案二

五、第三个答案
先建立一个可以生成连续日期序列的view
create view test2_date_seq as
select (select min(rq) from test2)+rownum-1 rq from all_objects where rownum<=(select max(rq)-min(rq)+1 from test2)
再建立一个view
create or replace view test2_ext as
select t2.rq,nvl(t1.income,0) income,nvl(t1.outcome,0) outcome from test2 t1,test2_date_seq t2
where t2.rq=t1.rq(+)

再建立地三个view，也是答案
create view result3(rq,income,outcome,balance) as
select t3.rq,sum(t3.income),sum(t3.outcome),max(dif3) from
(
select t1.row1,t1.rq,t1.income,t1.outcome,sum(t2.dif2) dif3 from
(select rownum row1,rq,income,outcome,income-outcome dif1 from test2_ext) t1,
(select rownum row2,rq,income-outcome dif2 from test2_ext) t2
where t1.row1 >= t2.row2
group by t1.row1,t1.rq,t1.income,t1.outcome
) t3
group by t3.rq
__________________
hold a minute, Let ME THINK..


--------------------------------------------------------------------------------
由 nanquanc 于 02-08-08 22:43 发表: 

我的方法
现有表 sum
如下
M CNT INER OUER
- --------- --------- ---------
1 1 5 5
2 6 15 5
3 1 10 5
4 1 10 5
5 1 40 5

SQL> select c.month,
SUM(C.INER),
SUM(C.OUER),