求个SQL的写法,看看有没有比较有效率的!

cosio 2009-11-05 11:35:56
数据向后相加:
--data:
2000 1 1
-1000 1 2
-3000 1 3
2000 1 4
9000 1 5

--result:
2000 1 1
0 1 2
0 1 3
0 1 4
7000 1 5

--TEST DATA:
with temp as
(
select 2000 a,1 b,1 c from dual
union all
select -1000,1,2 from dual
union all
select -3000,1,3 from dual
union all
select 2000,1,4 from dual
union all
select 9000,1,5 from dual
)

...全文
205 34 打赏 收藏 转发到动态 举报
写回复
用AI写文章
34 条回复
切换为时间正序
请发表友善的回复…
发表回复
liusong_china 2009-11-09
  • 打赏
  • 举报
回复
[Quote=引用 33 楼 wildwave 的回复:]
多增加几行就又有问题了
A B C RESULT
2000 1 1 2000
-1000 1 2 0
-1000 1 3 0
2000 1 4 0
9000 1 5 9000
-1000 1 6 0
-3000 1 7 0
1000 1 8 0
5000 1 9 2000
-2000 1 10 0
-1000 1 11 0
2000 1 12 0
2000 1 13 1000
-2000 1 14 0
1000 1 15 0
2000 1 16 1000
0 1 17 0
-9000 1 18 -9000
20000 1 19 12000

很厉害了。有时间好好研究一下
[/Quote]

还是排序的时候出了点问题。。。。

09:10:10 scott@TUNGKONG> with temp as
09:10:19 2 (
09:10:19 3 select 2000 a,1 b,1 c from dual
09:10:19 4 union all
09:10:19 5 select -1000,1,2 from dual
09:10:19 6 union all
09:10:19 7 select -1000,1,3 from dual
09:10:19 8 union all
09:10:19 9 select 2000,1,4 from dual
09:10:19 10 union all
09:10:19 11 select 9000,1,5 from dual
09:10:19 12 union all
09:10:19 13 select -1000,1,6 from dual
09:10:19 14 union all
09:10:19 15 select -3000,1,7 from dual
09:10:19 16 union all
09:10:19 17 select 1000,1,8 from dual
09:10:19 18 union all
09:10:19 19 select 5000,1,9 from dual
09:10:19 20 union all
09:10:19 21 select -2000,1,10 from dual
09:10:19 22 union all
09:10:19 23 select -1000,1,11 from dual
09:10:19 24 union all
09:10:19 25 select 2000,1,12 from dual
09:10:19 26 union all
09:10:19 27 select 2000,1,13 from dual
09:10:19 28 union all
09:10:19 29 select -2000,1,14 from dual
09:10:19 30 union all
09:10:19 31 select 1000,1,15 from dual
09:10:19 32 union all
09:10:19 33 select 2000,1,16 from dual
09:10:19 34 union all
09:10:19 35 select 0,1,17 from dual
09:10:19 36 union all
09:10:19 37 select -9000,1,18 from dual
09:10:19 38 union all
09:10:19 39 select 20000,1,19 from dual
09:10:19 40 )
09:10:19 41 select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end result from temp x left join
09:10:19 42 (select b,c,sum(a) over(partition by f order by c) d from (select distinct a,b,c,first_value(f) over(partition by b,c order by b,c,f) f from
09:10:19 43 (select x.a,x.b,x.c,nvl(f,x.b) f from temp x left join (select b,c,lpad(b,c,'*') f from (
09:10:19 44 select a,b,c,d,lag(d) over(partition by b order by c) e from (
09:10:19 45 select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end d from temp x left join
09:10:19 46 (select b,c,sum(a) over(partition by b order by c) d from temp,(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
09:10:19 47 on x.b = y.b and x.c = y.c order by x.b,x.c))
09:10:19 48 where d < 0 and e >= 0) y on x.b = y.b and x.c >= y.c order by x.b,x.c,f) order by b,c),
09:10:19 49 (select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
09:10:19 50 on x.b = y.b and x.c = y.c order by x.b,x.c;

A B C RESULT
---------- ---------- ---------- ----------
2000 1 1 2000
-1000 1 2 0
-1000 1 3 0
2000 1 4 0
9000 1 5 9000
-1000 1 6 0
-3000 1 7 0
1000 1 8 0
5000 1 9 2000
-2000 1 10 0
-1000 1 11 0
2000 1 12 0
2000 1 13 1000
-2000 1 14 0
1000 1 15 0
2000 1 16 1000
0 1 17 0
-9000 1 18 0
20000 1 19 11000

已选择19行。

已用时间: 00: 00: 00.04
小灰狼W 2009-11-07
  • 打赏
  • 举报
回复
多增加几行就又有问题了
A B C RESULT
2000 1 1 2000
-1000 1 2 0
-1000 1 3 0
2000 1 4 0
9000 1 5 9000
-1000 1 6 0
-3000 1 7 0
1000 1 8 0
5000 1 9 2000
-2000 1 10 0
-1000 1 11 0
2000 1 12 0
2000 1 13 1000
-2000 1 14 0
1000 1 15 0
2000 1 16 1000
0 1 17 0
-9000 1 18 -9000
20000 1 19 12000

很厉害了。有时间好好研究一下
小灰狼W 2009-11-06
  • 打赏
  • 举报
回复
好像还是有问题啊

A B C RESULT
2000 1 1 2000
-1000 1 2 0
-1000 1 3 0
2000 1 4 0
9000 1 5 9000
-1000 1 6 0
-3000 1 7 0
1000 1 8 0
5000 1 9 2000
-2000 1 10 0
-1000 1 11 0
2000 1 12 2000

最后一个,应该为0的。再添一条,就变成这样了

A B C RESULT
2000 1 1 2000
-1000 1 2 0
-1000 1 3 0
2000 1 4 0
9000 1 5 9000
-1000 1 6 0
-3000 1 7 0
1000 1 8 0
5000 1 9 2000
-2000 1 10 -2000
-1000 1 11 0
2000 1 12 1000
2000 1 13 2000
liusong_china 2009-11-06
  • 打赏
  • 举报
回复
逻辑有点乱了,试试这个吧,看看可以吗。。。。。。。。
liusong_china 2009-11-06
  • 打赏
  • 举报
回复
10:40:15 system@TUNGKONG> with temp as
10:40:28 2 (
10:40:28 3 select 4000 a,1 b,1 c from dual
10:40:28 4 union all
10:40:28 5 select -2000,1,2 from dual
10:40:28 6 union all
10:40:28 7 select 5000,1,3 from dual
10:40:28 8 union all
10:40:28 9 select 4000,1,4 from dual
10:40:28 10 union all
10:40:28 11 select 9000,1,5 from dual
10:40:28 12 union all
10:40:28 13 select -1000,1,6 from dual
10:40:28 14 union all
10:40:28 15 select -3000,1,7 from dual
10:40:28 16 union all
10:40:28 17 select 2000,1,8 from dual
10:40:28 18 union all
10:40:28 19 select 9000,1,9 from dual
10:40:28 20 )
10:40:28 21 select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end result from temp x left join
10:40:28 22 (select b,c,sum(a) over(partition by f order by c) d from (select distinct a,b,c,first_value(f) over(partition by b,c) f from
10:40:28 23 (select x.a,x.b,x.c,nvl(f,x.b) f from temp x left join (select b,c,lpad(b,c,'*') f from (
10:40:28 24 select a,b,c,d,lag(d) over(partition by b order by c) e from (
10:40:28 25 select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end d from temp x left join
10:40:28 26 (select b,c,sum(a) over(partition by b order by c) d from temp,(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
10:40:28 27 on x.b = y.b and x.c = y.c order by x.b,x.c))
10:40:28 28 where d < 0 and e > 0) y on x.b = y.b and x.c >= y.c order by x.b,x.c) order by b,c),
10:40:28 29 (select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
10:40:28 30 on x.b = y.b and x.c = y.c order by x.b,x.c;

A B C RESULT
---------- ---------- ---------- ----------
4000 1 1 4000
-2000 1 2 0
5000 1 3 3000
4000 1 4 4000
9000 1 5 9000
-1000 1 6 0
-3000 1 7 0
2000 1 8 0
9000 1 9 7000

已选择9行。

已用时间: 00: 00: 00.35
10:40:29 system@TUNGKONG> with temp as
10:40:40 2 (
10:40:40 3 select 4000 a,1 b,1 c from dual
10:40:40 4 union all
10:40:40 5 select -2000,1,2 from dual
10:40:40 6 union all
10:40:40 7 select 1000,1,3 from dual
10:40:40 8 union all
10:40:40 9 select -1000,1,4 from dual
10:40:40 10 union all
10:40:40 11 select -3000,1,5 from dual
10:40:40 12 union all
10:40:40 13 select 2000,1,6 from dual
10:40:40 14 union all
10:40:40 15 select 9000,1,7 from dual
10:40:40 16 union all
10:40:40 17 select -1000,1,8 from dual
10:40:40 18 union all
10:40:40 19 select -3000,1,9 from dual
10:40:40 20 union all
10:40:40 21 select 2000,1,10 from dual
10:40:40 22 union all
10:40:40 23 select 9000,1,11 from dual
10:40:40 24 )
10:40:41 25 select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end result from temp x left join
10:40:50 26 (select b,c,sum(a) over(partition by f order by c) d from (select distinct a,b,c,first_value(f) over(partition by b,c) f from
10:40:50 27 (select x.a,x.b,x.c,nvl(f,x.b) f from temp x left join (select b,c,lpad(b,c,'*') f from (
10:40:50 28 select a,b,c,d,lag(d) over(partition by b order by c) e from (
10:40:50 29 select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end d from temp x left join
10:40:50 30 (select b,c,sum(a) over(partition by b order by c) d from temp,(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
10:40:50 31 on x.b = y.b and x.c = y.c order by x.b,x.c))
10:40:50 32 where d < 0 and e > 0) y on x.b = y.b and x.c >= y.c order by x.b,x.c) order by b,c),
10:40:50 33 (select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
10:40:50 34 on x.b = y.b and x.c = y.c order by x.b,x.c;

A B C RESULT
---------- ---------- ---------- ----------
4000 1 1 4000
-2000 1 2 0
1000 1 3 0
-1000 1 4 0
-3000 1 5 0
2000 1 6 0
9000 1 7 6000
-1000 1 8 0
-3000 1 9 0
2000 1 10 0
9000 1 11 7000

已选择11行。

已用时间: 00: 00: 00.04
10:40:51 system@TUNGKONG> with temp as
10:41:02 2 (
10:41:02 3 select -4000 a,1 b,1 c from dual
10:41:02 4 union all
10:41:02 5 select 2000,1,2 from dual
10:41:02 6 union all
10:41:02 7 select 5000,1,3 from dual
10:41:02 8 union all
10:41:02 9 select -4000,1,4 from dual
10:41:02 10 union all
10:41:02 11 select 9000,1,5 from dual
10:41:02 12 )
10:41:02 13 select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end result from temp x left join
10:41:10 14 (select b,c,sum(a) over(partition by f order by c) d from (select distinct a,b,c,first_value(f) over(partition by b,c) f from
10:41:10 15 (select x.a,x.b,x.c,nvl(f,x.b) f from temp x left join (select b,c,lpad(b,c,'*') f from (
10:41:10 16 select a,b,c,d,lag(d) over(partition by b order by c) e from (
10:41:10 17 select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end d from temp x left join
10:41:10 18 (select b,c,sum(a) over(partition by b order by c) d from temp,(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
10:41:10 19 on x.b = y.b and x.c = y.c order by x.b,x.c))
10:41:10 20 where d < 0 and e > 0) y on x.b = y.b and x.c >= y.c order by x.b,x.c) order by b,c),
10:41:10 21 (select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
10:41:10 22 on x.b = y.b and x.c = y.c order by x.b,x.c;

A B C RESULT
---------- ---------- ---------- ----------
-4000 1 1 0
2000 1 2 0
5000 1 3 3000
-4000 1 4 0
9000 1 5 5000

已用时间: 00: 00: 00.03
liusong_china 2009-11-06
  • 打赏
  • 举报
回复
[Quote=引用 24 楼 cosio 的回复:]
按理说这个抓出来的数据应该是:
-4000 1 1 0
2000 1 2 0
5000 1 3 3000
-4000 1 4 0
9000 1 55000
[/Quote]
select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end result from temp x left join
(select b,c,sum(a) over(partition by f order by c) d from (select distinct a,b,c,first_value(f) over(partition by b,c) f from
(select x.a,x.b,x.c,nvl(f,x.b) f from temp x left join (select b,c,lpad(b,c,'*') f from (
select a,b,c,d,lag(d) over(partition by b order by c) e from (
select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end d from temp x left join
(select b,c,sum(a) over(partition by b order by c) d from temp,(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
on x.b = y.b and x.c = y.c order by x.b,x.c))
where d < 0 and e > 0) y on x.b = y.b and x.c >= y.c order by x.b,x.c) order by b,c),
(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
on x.b = y.b and x.c = y.c order by x.b,x.c;
cosio 2009-11-06
  • 打赏
  • 举报
回复

with temp as
(
select -4000 a,1 b,1 c from dual
union all
select 2000,1,2 from dual
union all
select 5000,1,3 from dual
union all
select -4000,1,4 from dual
union all
select 9000,1,5 from dual
)
select x.a,x.b,x.c,case when y.d < 0 then 0 when y.d is null or y.d > x.a then x.a else y.d end result from temp x left join
(select b,c,sum(a) over(partition by f order by c) d from (select distinct a,b,c,first_value(f) over(partition by b,c) f from
(select x.a,x.b,x.c,nvl(f,x.b) f from temp x left join (select b,c,lpad(b,c,'*') f from (
select a,b,c,d,lag(d) over(partition by b order by c) e from (
select x.a,x.b,x.c,case when y.d < 0 then 0 when y.d is null or y.d > x.a then x.a else y.d end d from temp x left join
(select b,c,sum(a) over(partition by b order by c) d from temp,(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
on x.b = y.b and x.c = y.c order by x.b,x.c))
where d < 0 and e > 0) y on x.b = y.b and x.c >= y.c order by x.b,x.c) order by b,c),
(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
on x.b = y.b and x.c = y.c order by x.b,x.c;


这个数据的测试也不行!
-4000 1 1 0
2000 1 2 0
5000 1 3 3000
-4000 1 4 0
9000 1 5 8000

按理说这个抓出来的数据应该是:
-4000 1 1 0
2000 1 2 0
5000 1 3 3000
-4000 1 4 0
9000 1 5 5000
liusong_china 2009-11-06
  • 打赏
  • 举报
回复
楼主测试一下是否满足需求。。。。。。。。。。

大家也讨论一下,看看有什么疏漏的地方。。。。
卖萌滴小妹妹 2009-11-06
  • 打赏
  • 举报
回复
学习中
liusong_china 2009-11-06
  • 打赏
  • 举报
回复
09:53:31 scott@TUNGKONG> with temp as
09:53:41 2 (
09:53:41 3 select 4000 a,1 b,1 c from dual
09:53:41 4 union all
09:53:41 5 select -2000,1,2 from dual
09:53:41 6 union all
09:53:41 7 select 5000,1,3 from dual
09:53:41 8 union all
09:53:41 9 select 4000,1,4 from dual
09:53:41 10 union all
09:53:41 11 select 9000,1,5 from dual
09:53:41 12 union all
09:53:41 13 select -1000,1,6 from dual
09:53:41 14 union all
09:53:41 15 select -3000,1,7 from dual
09:53:41 16 union all
09:53:41 17 select 2000,1,8 from dual
09:53:41 18 union all
09:53:41 19 select 9000,1,9 from dual
09:53:41 20 )
09:53:41 21 select x.a,x.b,x.c,case when y.d < 0 then 0 when y.d is null or y.d > x.a then x.a else y.d end result from temp x left join
09:53:41 22 (select b,c,sum(a) over(partition by f order by c) d from (select distinct a,b,c,first_value(f) over(partition by b,c) f from
09:53:41 23 (select x.a,x.b,x.c,nvl(f,x.b) f from temp x left join (select b,c,lpad(b,c,'*') f from (
09:53:41 24 select a,b,c,d,lag(d) over(partition by b order by c) e from (
09:53:41 25 select x.a,x.b,x.c,case when y.d < 0 then 0 when y.d is null or y.d > x.a then x.a else y.d end d from temp x left join
09:53:41 26 (select b,c,sum(a) over(partition by b order by c) d from temp,(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
09:53:41 27 on x.b = y.b and x.c = y.c order by x.b,x.c))
09:53:41 28 where d < 0 and e > 0) y on x.b = y.b and x.c >= y.c order by x.b,x.c) order by b,c),
09:53:41 29 (select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
09:53:41 30 on x.b = y.b and x.c = y.c order by x.b,x.c;

A B C RESULT
---------- ---------- ---------- ----------
4000 1 1 4000
-2000 1 2 0
5000 1 3 3000
4000 1 4 4000
9000 1 5 9000
-1000 1 6 0
-3000 1 7 0
2000 1 8 0
9000 1 9 7000

已选择9行。

已用时间: 00: 00: 00.14
09:53:44 scott@TUNGKONG> with temp as
09:53:59 2 (
09:53:59 3 select 4000 a,1 b,1 c from dual
09:53:59 4 union all
09:53:59 5 select -2000,1,2 from dual
09:53:59 6 union all
09:53:59 7 select 1000,1,3 from dual
09:53:59 8 union all
09:53:59 9 select -1000,1,4 from dual
09:53:59 10 union all
09:53:59 11 select -3000,1,5 from dual
09:53:59 12 union all
09:53:59 13 select 2000,1,6 from dual
09:53:59 14 union all
09:53:59 15 select 9000,1,7 from dual
09:53:59 16 union all
09:53:59 17 select -1000,1,8 from dual
09:53:59 18 union all
09:53:59 19 select -3000,1,9 from dual
09:53:59 20 union all
09:53:59 21 select 2000,1,10 from dual
09:53:59 22 union all
09:53:59 23 select 9000,1,11 from dual
09:53:59 24 )
09:54:08 25 select x.a,x.b,x.c,case when y.d < 0 then 0 when y.d is null or y.d > x.a then x.a else y.d end result from temp x left join
09:54:09 26 (select b,c,sum(a) over(partition by f order by c) d from (select distinct a,b,c,first_value(f) over(partition by b,c) f from
09:54:09 27 (select x.a,x.b,x.c,nvl(f,x.b) f from temp x left join (select b,c,lpad(b,c,'*') f from (
09:54:09 28 select a,b,c,d,lag(d) over(partition by b order by c) e from (
09:54:09 29 select x.a,x.b,x.c,case when y.d < 0 then 0 when y.d is null or y.d > x.a then x.a else y.d end d from temp x left join
09:54:09 30 (select b,c,sum(a) over(partition by b order by c) d from temp,(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
09:54:09 31 on x.b = y.b and x.c = y.c order by x.b,x.c))
09:54:09 32 where d < 0 and e > 0) y on x.b = y.b and x.c >= y.c order by x.b,x.c) order by b,c),
09:54:09 33 (select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
09:54:09 34 on x.b = y.b and x.c = y.c order by x.b,x.c;

A B C RESULT
---------- ---------- ---------- ----------
4000 1 1 4000
-2000 1 2 0
1000 1 3 0
-1000 1 4 0
-3000 1 5 0
2000 1 6 0
9000 1 7 6000
-1000 1 8 0
-3000 1 9 0
2000 1 10 0
9000 1 11 7000

已选择11行。

已用时间: 00: 00: 00.09
liusong_china 2009-11-06
  • 打赏
  • 举报
回复
select x.a,x.b,x.c,case when y.d < 0 then 0 when y.d is null or y.d > x.a then x.a else y.d end result from temp x left join
(select b,c,sum(a) over(partition by f order by c) d from (select distinct a,b,c,first_value(f) over(partition by b,c) f from
(select x.a,x.b,x.c,nvl(f,x.b) f from temp x left join (select b,c,lpad(b,c,'*') f from (
select a,b,c,d,lag(d) over(partition by b order by c) e from (
select x.a,x.b,x.c,case when y.d < 0 then 0 when y.d is null or y.d > x.a then x.a else y.d end d from temp x left join
(select b,c,sum(a) over(partition by b order by c) d from temp,(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
on x.b = y.b and x.c = y.c order by x.b,x.c))
where d < 0 and e > 0) y on x.b = y.b and x.c >= y.c order by x.b,x.c) order by b,c),
(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
on x.b = y.b and x.c = y.c order by x.b,x.c;
cosio 2009-11-06
  • 打赏
  • 举报
回复
因为当时的需求只有十个以内的数据就可以了!
liusong_china 2009-11-06
  • 打赏
  • 举报
回复
wildwave(狂浪) ,再帮忙看看还有什么问题没。。。。。。。

帖子已经结了,估计楼主不大会关注了。。。。。。。。。
liusong_china 2009-11-06
  • 打赏
  • 举报
回复
14:46:58 scott@TUNGKONG> with temp as
14:47:08 2 (
14:47:08 3 select 2000 a,1 b,1 c from dual
14:47:08 4 union all
14:47:08 5 select -1000,1,2 from dual
14:47:08 6 union all
14:47:08 7 select -1000,1,3 from dual
14:47:08 8 union all
14:47:08 9 select 2000,1,4 from dual
14:47:08 10 union all
14:47:08 11 select 9000,1,5 from dual
14:47:08 12 union all
14:47:08 13 select -1000,1,6 from dual
14:47:08 14 union all
14:47:08 15 select -3000,1,7 from dual
14:47:08 16 union all
14:47:08 17 select 1000,1,8 from dual
14:47:08 18 union all
14:47:08 19 select 5000,1,9 from dual
14:47:08 20 union all
14:47:08 21 select -2000,1,10 from dual
14:47:08 22 union all
14:47:08 23 select -1000,1,11 from dual
14:47:08 24 union all
14:47:08 25 select 2000,1,12 from dual
14:47:08 26 union all
14:47:08 27 select 2000,1,13 from dual
14:47:08 28 )
14:47:08 29 select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end result from temp x left join
14:47:08 30 (select b,c,sum(a) over(partition by f order by c) d from (select distinct a,b,c,first_value(f) over(partition by b,c) f from
14:47:08 31 (select x.a,x.b,x.c,nvl(f,x.b) f from temp x left join (select b,c,lpad(b,c,'*') f from (
14:47:08 32 select a,b,c,d,lag(d) over(partition by b order by c) e from (
14:47:08 33 select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end d from temp x left join
14:47:08 34 (select b,c,sum(a) over(partition by b order by c) d from temp,(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
14:47:08 35 on x.b = y.b and x.c = y.c order by x.b,x.c))
14:47:08 36 where d < 0 and e > 0) y on x.b = y.b and x.c >= y.c order by x.b,x.c,f) order by b,c),
14:47:08 37 (select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
14:47:08 38 on x.b = y.b and x.c = y.c order by x.b,x.c;

A B C RESULT
---------- ---------- ---------- ----------
2000 1 1 2000
-1000 1 2 0
-1000 1 3 0
2000 1 4 0
9000 1 5 9000
-1000 1 6 0
-3000 1 7 0
1000 1 8 0
5000 1 9 2000
-2000 1 10 0
-1000 1 11 0
2000 1 12 0
2000 1 13 1000

已选择13行。

已用时间: 00: 00: 00.09
14:47:09 scott@TUNGKONG> with temp as
14:47:32 2 (
14:47:32 3 select 2000 a,1 b,1 c from dual
14:47:32 4 union all
14:47:32 5 select -1000,1,2 from dual
14:47:32 6 union all
14:47:32 7 select -1000,1,3 from dual
14:47:32 8 union all
14:47:32 9 select 2000,1,4 from dual
14:47:32 10 union all
14:47:32 11 select 9000,1,5 from dual
14:47:32 12 union all
14:47:32 13 select -1000,1,6 from dual
14:47:32 14 union all
14:47:32 15 select -3000,1,7 from dual
14:47:32 16 union all
14:47:32 17 select 1000,1,8 from dual
14:47:32 18 union all
14:47:32 19 select 5000,1,9 from dual
14:47:32 20 union all
14:47:32 21 select -2000,1,10 from dual
14:47:32 22 union all
14:47:32 23 select -1000,1,11 from dual
14:47:32 24 union all
14:47:32 25 select 2000,1,12 from dual
14:47:32 26 )
14:47:32 27 select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end result from temp x left join
14:47:32 28 (select b,c,sum(a) over(partition by f order by c) d from (select distinct a,b,c,first_value(f) over(partition by b,c) f from
14:47:32 29 (select x.a,x.b,x.c,nvl(f,x.b) f from temp x left join (select b,c,lpad(b,c,'*') f from (
14:47:32 30 select a,b,c,d,lag(d) over(partition by b order by c) e from (
14:47:32 31 select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end d from temp x left join
14:47:32 32 (select b,c,sum(a) over(partition by b order by c) d from temp,(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
14:47:32 33 on x.b = y.b and x.c = y.c order by x.b,x.c))
14:47:32 34 where d < 0 and e > 0) y on x.b = y.b and x.c >= y.c order by x.b,x.c,f) order by b,c),
14:47:32 35 (select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
14:47:32 36 on x.b = y.b and x.c = y.c order by x.b,x.c;

A B C RESULT
---------- ---------- ---------- ----------
2000 1 1 2000
-1000 1 2 0
-1000 1 3 0
2000 1 4 0
9000 1 5 9000
-1000 1 6 0
-3000 1 7 0
1000 1 8 0
5000 1 9 2000
-2000 1 10 0
-1000 1 11 0
2000 1 12 0

已选择12行。

已用时间: 00: 00: 00.04
liusong_china 2009-11-06
  • 打赏
  • 举报
回复
[Quote=引用 28 楼 wildwave 的回复:]
好像还是有问题啊

A B C RESULT
2000 1 1 2000
-1000 1 2 0
-1000 1 3 0
2000 1 4 0
9000 1 5 9000
-1000 1 6 0
-3000 1 7 0
1000 1 8 0
5000 1 9 2000
-2000 1 10 0
-1000 1 11 0
2000 1 12 2000

最后一个,应该为0的。再添一条,就变成这样了

A B C RESULT
2000 1 1 2000
-1000 1 2 0
-1000 1 3 0
2000 1 4 0
9000 1 5 9000
-1000 1 6 0
-3000 1 7 0
1000 1 8 0
5000 1 9 2000
-2000 1 10 -2000
-1000 1 11 0
2000 1 12 1000
2000 1 13 2000
[/Quote]

不好意思,排序的时候漏了个字段。

select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end result from temp x left join
(select b,c,sum(a) over(partition by f order by c) d from (select distinct a,b,c,first_value(f) over(partition by b,c) f from
(select x.a,x.b,x.c,nvl(f,x.b) f from temp x left join (select b,c,lpad(b,c,'*') f from (
select a,b,c,d,lag(d) over(partition by b order by c) e from (
select x.a,x.b,x.c,case when y.d < 0 and y.d <= x.a then 0 when y.d is null or y.d > x.a then x.a else y.d end d from temp x left join
(select b,c,sum(a) over(partition by b order by c) d from temp,(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
on x.b = y.b and x.c = y.c order by x.b,x.c))
where d < 0 and e > 0) y on x.b = y.b and x.c >= y.c order by x.b,x.c,f) order by b,c),
(select b b2,min(c) c2 from temp where a < 0 group by b) where b = b2 and c >= c2) y
on x.b = y.b and x.c = y.c order by x.b,x.c;
cosio 2009-11-05
  • 打赏
  • 举报
回复
A B C
2000 1 1
-1000 1 2
-3000 1 3
2000 1 4
9000 1 5

描述一下!
1.第一行,A>0 这行的数据不变
2.第二行,A<0 变为0,这时就要和第三行相加,如果第二行A>0 那值也不变
3.第三行,A<0 变为0,
4,第四行,因为A的值,是第二行(-1000)+第三行(-3000)+第四行(1000)=-2000<0 所以也要变为0
5.第五行,就是A的值,就是-2000+9000=7000;
cosio 2009-11-05
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 cosio 的回复:]
with temp as
(
select 2000 a,1 b,1 c  from dual
union all
select -1000,1,2  from dual
union all
select -1000,1,3  from dual
union all
select 2000,1,4  from dual
union all
select 9000,1,5  from dual
)
select a,b,c,d,case when a <0 then 0 else sum(a)over(partition by d order by d)end  e
            from
            (
            select a.a a,a.b b,a.c c,b.c d,row_number()over(partition by a.a,a.c order by a.c) rn from
            (select a,b,c from temp a) a
            ,
            (select c c
                from
                (
                      select a,b,c,(case when a <0 then 0 else a end ) d from temp
                )
                where d>0
            )b
            where a.c <=b.c
            order by b.c
            )
            where rn=1
            order by c


--result:

2000 1 1 1 2000
-1000 1 2 4 0
-1000 1 3 4 0
2000 1 4 4 0
9000 1 5 5 9000


其中最后一列就是我的要求的数据


[/Quote]

这个事例中,如果负数的值比较大的话,数据就不对!
liusong_china 2009-11-05
  • 打赏
  • 举报
回复
没看懂楼主要表达什么意思。。。。。
cosio 2009-11-05
  • 打赏
  • 举报
回复
就是说如果是负数,数据就和下一行的值相加,然后把自己行变0;
cosio 2009-11-05
  • 打赏
  • 举报
回复
with temp as
(
select 2000 a,1 b,1 c from dual
union all
select -1000,1,2 from dual
union all
select -1000,1,3 from dual
union all
select 2000,1,4 from dual
union all
select 9000,1,5 from dual
)
select a,b,c,d,case when a<0 then 0 else sum(a)over(partition by d order by d)end e
from
(
select a.a a,a.b b,a.c c,b.c d,row_number()over(partition by a.a,a.c order by a.c) rn from
(select a,b,c from temp a) a
,
(select c c
from
(
select a,b,c,(case when a<0 then 0 else a end ) d from temp
)
where d>0
)b
where a.c<=b.c
order by b.c
)
where rn=1
order by c


--result:

2000 1 1 1 2000
-1000 1 2 4 0
-1000 1 3 4 0
2000 1 4 4 0
9000 1 5 5 9000


其中最后一列就是我的要求的数据

加载更多回复(14)

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧