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

oracle问题集

(E.INER-E.OUER)
from (select a.month,
sum(a.cnt),
sum(b.cnt) cnt_1,
SUM(B.INER) INER ,
SUm(B.OUER) OUER
from sum a,
(select sum.
month,
sum(sum.cnt) cnt,
SUM(INER) INER,
SUM(OUER) OUER
from sum 
group by sum.month) b
where a.month>=b.month
group by a.month) e,
sum c
where c.month=e.month
group by c.month,e. cnt_1,(E.INER-E.OUER)
0 /

M SUM(C.INER) SUM(C.OUER) (E.INER-E.OUER)
- ----------- ----------- ---------------
1 5 5 0
2 15 5 10
3 10 5 15
4 10 5 20
5 40 5 55
__________________
ANDREW


--------------------------------------------------------------------------------
由 biti_rainy 于 02-08-08 23:50 发表: 
nanquanc

如果你的 M 这个字段的值不是连续的，你这个查询能解决么？
__________________
I love oracle
But i hate IT


--------------------------------------------------------------------------------
由 Seraphim 于 02-08-11 09:21 发表: 
Re: 利用all_objects来获得连续的number，想法不错


quote:
--------------------------------------------------------------------------------
最初由 darkstorm 发布
关键的一句在于
select ((select min(a) from test) + rownum - 1) aa from all_objects
where rownum <= (select max(a) - min(a) +1 from test)

给了我一个很好的思路，谢谢 
--------------------------------------------------------------------------------



如果你的max(a)-max(a)大于all_objects的记录数，怎么办？所以我不太明白为什么要选all_objects表。我的all_objects表中只有4000多数据
__________________
不可否认我的ORACLE很差，但是我正在学习，而且还很努力。
023 ../.. 这周末开始。总算要开始了。：）
024 ../..
025 ../..
026 ../..
001 ../..



--------------------------------------------------------------------------------
由 Seraphim 于 02-08-11 09:26 发表: 

用from all_objects,all_objects倒是可以缓解一下
__________________
不可否认我的ORACLE很差，但是我正在学习，而且还很努力。
023 ../.. 这周末开始。总算要开始了。：）
024 ../..
025 ../..
026 ../..
001 ../..



--------------------------------------------------------------------------------
由 Seraphim 于 02-08-11 09:36 发表: 

不过我测了一下，如果用biti_rainy的方法，如果max(a)-min(a)在10000的时候，执行的时间几乎没有办法忍受啊，我等了5分钟都没有执行完。
__________________
不可否认我的ORACLE很差，但是我正在学习，而且还很努力。
023 ../.. 这周末开始。总算要开始了。：）
024 ../..
025 ../..
026 ../..
001 ../..



--------------------------------------------------------------------------------
由 Seraphim 于 02-08-11 09:48 发表: 

而且如果是这样：
A B C
---------- ---------- ----------
1 1 0
2 2 1
4 4 2
6 1 3
6 2 1
8 4 4

结果会是：
1 1 0 1
2 2 1 2
3 0 0 2
4 4 2 4
5 0 0 4
6 1 3 3
6 2 1 3
7 0 0 3
8 4 4 3

而不是题目要求的：
1 1 0 1
2 2 1 2
3 0 0 2
4 4 2 4
5 0 0 4
6 3 4 3
7 0 0 3
8 4 4 3
__________________
不可否认我的ORACLE很差，但是我正在学习，而且还很努力。
023 ../.. 这周末开始。总算要开始了。：）
024 ../..
025 ../..
026 ../..
001 ../..



--------------------------------------------------------------------------------
由 biti_rainy 于 02-08-11 18:22 发表: 
hehe

1: 运行性能的问题，如果你是 816以上版本采用新的分析函数，肯定会有改观

2：至于你的A字段上有重复的问题，因为在先前的题目中并没有提出来，并且根据题意，是不会有重复的。假如你真要重复，这也是很好解决的，先合并成一个就ok了。其实重要的是思路是吧，具体的这些问题都很好解决的

BTW：建议你阅读816以上的分析函数，将有很大的收获
__________________
I love oracle
But i hate IT


--------------------------------------------------------------------------------
由 nanquanc 于 02-08-12 21:16 发表: 
to biti_rainy 如果你的 M 这个字段的值不是连续的，你这个查询能解决么？

可以
SQL> select * from sum;

M CNT INER OUER
- --------- --------- ---------
1 1 5 5
2 6 15 5
4 1 10 5
5 1 40 5


SQL> select c.month,
2 SUM(C.INER),
3 SUM(C.OUER),
4 (E.INER-E.OUER)
5 from (select a.month,
6 sum(a.cnt),
7 sum(b.cnt) cnt_1,
8 SUM(B.INER) INER ,
9 SUm(B.OUER) OUER
10 from sum a,
11 (select sum.
12 month,
13 sum(sum.cnt) cnt,
14 SUM(INER) INER,
15 SUM(OUER) OUER
16 from sum 
17 group by sum.month) b
18 where a.month>=b.month
19 group by a.month) e,
20 sum c
21 where c.month=e.month
22 group by c.month,e. cnt_1,(E.INER-E.OUER)
23 
SQL> /

M SUM(C.INER) SUM(C.OUER) (E.INER-E.OUER)
- ----------- ----------- ---------------
1 5 5 0
2 15 5 10
4 10 5 15
5 40 5 50
__________________
ANDREW


--------------------------------------------------------------------------------
由 biti_rainy 于 02-08-12 21:38 发表: 
nanquanc

我说的是第3个问题
你还是没有明白
就是不连续的，需要你去补连续 ，ok?
__________________
I love oracle
But i hate IT


--------------------------------------------------------------------------------
由 chenwg 于 02-08-14 18:49 发表: 

问题改动一下：
TABLE如下
mdate begin income payout balance
2000-3-1 10 50 30 30
2000-3-2 30 45 60 15
2000-3-5 15 60 10 65

能否用SELECT語句得出以下結果
mdate begin income payout balance
2000-3-1 10 50 30 30
2000-3-2 30 45 60 15
2000-3-3 15 0 0 15
2000-3-4 15 0 0 15
2000-3-5 15 60 10 65

有时一个系统开始投入运行时初值并不为0，需要手工建一个初值
这个问题就是如此。
我想了好一会，也没想出来怎样才能把初值10加到每一个余额中？
请biti_rainy帮忙解决一下。


--------------------------------------------------------------------------------
由 biti_rainy 于 02-08-14 19:17 发表: 
这是一个简单的加减来解决的

当然如果你需要begin为当前行之前的N行处的值的话，需要使用分析函数本身来解决
我这里只是简单的这么处理了

SQL> select * from test;

A B C D
---------- ---------- ---------- --------------------
1 2 3 4
6 7 8 9
3 1 2 1

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
---------- ---------- ---------- ----------
1 2 3 -1
2 0 0 -1
3 1 2 -2
4 0 0 -2
5 0 0 -2
6 7 8 -3

已选择6行。

SQL> select tt2.tta, tt2.ttb, tt2.ttc,10 + 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
---------- ---------- ---------- ----------
1 2 3 9
2 0 0 9
3 1 2 8
4 0 0 8
5 0 0 8
6 7 8 7

已选择6行。

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

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

已选择6行。

SQL>
__________________
I love oracle
But i hate IT


--------------------------------------------------------------------------------
由 chenwg 于 02-08-14 19:45 发表: 

把常数10写到SELECT语句中恐怕不行吧？初值10是一个开发时并不知道的量，由用户输入。
而且若这个表放的是商品库存，里面会有很多商品，每一个商品的初始库存都是不一样的，这应该如何处理？

请指教


--------------------------------------------------------------------------------
由 biti_rainy 于 02-08-14 19:54 发表: 
你把它当一个变量传进去

至于你怎么读取到变量的值，那是你解决的问题了

仅仅把10替换成一个变量就可以了，不是么？
__________________
I love oracle
But i hate IT


--------------------------------------------------------------------------------
由 chenwg 于 02-08-14 20:12 发表: 

要查询所有商品的库存时，如何把每一个商品的初始库存量都放到变量中？


--------------------------------------------------------------------------------
由 chenwg 于 02-08-14 20:13 发表: 
Re: 你把它当一个变量传进去


quote:
--------------------------------------------------------------------------------
最初由 biti_rainy 发布
至于你怎么读取到变量的值，那是你解决的问题了

仅仅把10替换成一个变量就可以了，不是么？ 
--------------------------------------------------------------------------------




最重要的是仅用一条select 语句实现多个不同商品的查询！


--------------------------------------------------------------------------------
由 biti_rainy 于 02-08-14 20:44 发表: 
那也行啊

这里也可以嵌一个子查询啊

或者
先把这个结果集取出来
再跟初始值的做表连接/或者利用游标什么的来处理

总之，我以为：有具体的需求摆在面前，总是能解决的，重要的是适当的考虑效率问题
__________________
I love oracle
But i hate IT


--------------------------------------------------------------------------------
由 gyang 于 02-08-23 16:26 发表: 

各位，expert one on one 是什么东西，别笑，我是初学者。


--------------------------------------------------------------------------------
由 jlandzpa 于 02-08-26 23:05 发表: 

tom 写的一本书.
__________________
任弱水三千，我独取一瓢饮！
勿以善小而不为，勿以恶小而为之！
不要以为登上了山峰，你就征服了世界！
最近很忙！
比较烦！


--------------------------------------------------------------------------------
由 wuboss 于 02-08-27 19:35 发表: 
biti_rainy获取连续数字的方法是不错

可是必须要求all_objects的记录数大于max(a) - min(a)呀。
__________________

我是猫猫我怕谁！


--------------------------------------------------------------------------------
由 wuboss 于 02-08-27 22:39 发表: 

SELECT t1.日期, t1.收入, t1.支出, sum(t2.收入)-sum(t2.支出) 余额
FROM test t1,test t2 
WHERE t1.日期 >= t2.日期
GROUP BY t1.日期,t1.收入,t1.支出;
__________________

我是猫猫我怕谁！


--------------------------------------------------------------------------------
由 Leepan 于 02-08-28 23:34 发表: 
What is --------expert one on one ?

What is --------expert one on one ?


--------------------------------------------------------------------------------
由 jlandzpa 于 02-08-30 22:28 发表: 

tom 写的一本书，很不错的！ 有中文版的！
__________________
任弱水三千，我独取一瓢饮！
勿以善小而不为，勿以恶小而为之！
不要以为登上了山峰，你就征服了世界！
最近很忙！
比较烦！


--------------------------------------------------------------------------------
由 wwl007 于 02-09-06 19:45 发表: 
问题的解决方法！

高然是高手！真的见了高人！哈哈
我也有个方法
但是对于第三个问题真的佩服
select decode(t.a,t.b,t.c,
(select sum(b-c) 
from test 
where a<=t.a) d
from test t
__________________
活的好无奈


--------------------------------------------------------------------------------
由 wwl007 于 02-09-06 19:56 发表: 
我也使用它的来一个哈哈

select m.a,nvl(t.b,0),nvl(t.b,0),
(select sum(b-c) 
from test 
where a<=m.a) d
from test t,(select (select min(a) from test)+rownum-1a from all_objects where rownum<=(select max(a)-min(a)+1 from test)) m
where t.a(+)=m.a
__________________
活的好无奈


--------------------------------------------------------------------------------
由 mjct 于 02-09-22 04:25 发表: 

百试不爽~~~~~ 哈哈~~~~~~
__________________
//****************************************
//ITPUB狂热分子！
//****************************************


--------------------------------------------------------------------------------
由 卓玛 于 02-10-02 22:51 发表: 

收藏了!
可以给咱们做库存管理用(.
__________________
云对树说:我漫游了整个宇宙
树对云说:你的家在哪儿?
云对树说:孤身走我路,四海皆我家.


--------------------------------------------------------------------------------