数据向后相加:
--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
)
--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
)
(
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
其中最后一列就是我的要求的数据
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;
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
)
select x.a,x.b,x.c,case when y.d < 0 then 0 when y.d is null then x.a else y.d end result 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;
13:53:14 scott@TUNGKONG> with temp as
13:53:20 2 (
13:53:20 3 select 2000 a,1 b,1 c from dual
13:53:20 4 union all
13:53:20 5 select -1000,1,2 from dual
13:53:20 6 union all
13:53:20 7 select -3000,1,3 from dual
13:53:20 8 union all
13:53:20 9 select 2000,1,4 from dual
13:53:20 10 union all
13:53:20 11 select 9000,1,5 from dual
13:53:20 12 )
13:53:20 13 select x.a,x.b,x.c,case when y.d < 0 then 0 when y.d is null then x.a else y.d end result from temp x left join
13:53:20 14 (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
13:53:20 15 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
-3000 1 3 0
2000 1 4 0
9000 1 5 7000已用时间: 00: 00: 00.04
(
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
)这样子的数据要求的结果显示是:4000
0
3000
4000
9000
会出现正负交叉的情况吗？
如：
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
union all
select -1000,1,6 from dual
union all
select -3000,1,7 from dual
union all
select 2000,1,8 from dual
union all
select 9000,1,9 from dual
)
我的意思就是问你如果出现这种情况，你想要的结果是什么样子啊？
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
union all
select -1000,1,6 from dual
union all
select -3000,1,7 from dual
union all
select 2000,1,8 from dual
union all
select 9000,1,9 from dual
)
d的取值要从第一列递推来判断
但是树形查询又没找到相关的函数可以使用
用存储过程就比较简单
(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;
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
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
(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;
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
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
不好意思，排序的时候漏了个字段。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;
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
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很厉害了。有时间好好研究一下
还是排序的时候出了点问题。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