17,078
社区成员
发帖
与我相关
我的任务
分享
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
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
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;
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
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;
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
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;