给你做了个例子,不知道合不合心意:
create table test001 (c number,r1 number,r2 number,r3 number);
insert into test001 values(0,100,100,100);
insert into test001 values(1,101,101,101);
insert into test001 values(2,102,102,102);
insert into test001 values(3,103,103,103);
insert into test001 values(4,104,104,104);
insert into test001 values(5,105,105,105);
insert into test001 values(9,106,106,106);
insert into test001 values(8,107,107,107);
insert into test001 values(7,108,108,108);
insert into test001 values(6,109,109,109);
commit ;select C,(R1-S1),(R2-S2),(R3-S3) from
(
select test001.C as C,
test001.R1 as S1,
test001.R2 as S2,
test001.R3 as S3,
LEAD(r1, 1) OVER (ORDER BY c) AS R1,
LEAD(r2, 1) OVER (ORDER BY c) AS R2,
LEAD(r3, 1) OVER (ORDER BY c) AS R3
from test001
);执行结果为:SQL> select C,(R1-S1),(R2-S2),(R3-S3) from
2 (
3 select test001.C as C,
4 test001.R1 as S1,
5 test001.R2 as S2,
6 test001.R3 as S3,
7 LEAD(r1, 1) OVER (ORDER BY c) AS R1,
8 LEAD(r2, 1) OVER (ORDER BY c) AS R2,
9 LEAD(r3, 1) OVER (ORDER BY c) AS R3
10 from test001
11 ); C (R1-S1) (R2-S2) (R3-S3)
---------- ---------- ---------- ----------
0 1 1 1
1 1 1 1
2 1 1 1
3 1 1 1
4 1 1 1
5 4 4 4
6 -1 -1 -1
7 -1 -1 -1
8 -1 -1 -1
9已选择10行。
create table test001 (c number,r1 number,r2 number,r3 number);
insert into test001 values(0,100,100,100);
insert into test001 values(1,101,101,101);
insert into test001 values(2,102,102,102);
insert into test001 values(3,103,103,103);
insert into test001 values(4,104,104,104);
insert into test001 values(5,105,105,105);
insert into test001 values(9,106,106,106);
insert into test001 values(8,107,107,107);
insert into test001 values(7,108,108,108);
insert into test001 values(6,109,109,109);
commit ;select C,(R1-S1),(R2-S2),(R3-S3) from
(
select test001.C as C,
test001.R1 as S1,
test001.R2 as S2,
test001.R3 as S3,
LEAD(r1, 1) OVER (ORDER BY c) AS R1,
LEAD(r2, 1) OVER (ORDER BY c) AS R2,
LEAD(r3, 1) OVER (ORDER BY c) AS R3
from test001
);执行结果为:SQL> select C,(R1-S1),(R2-S2),(R3-S3) from
2 (
3 select test001.C as C,
4 test001.R1 as S1,
5 test001.R2 as S2,
6 test001.R3 as S3,
7 LEAD(r1, 1) OVER (ORDER BY c) AS R1,
8 LEAD(r2, 1) OVER (ORDER BY c) AS R2,
9 LEAD(r3, 1) OVER (ORDER BY c) AS R3
10 from test001
11 ); C (R1-S1) (R2-S2) (R3-S3)
---------- ---------- ---------- ----------
0 1 1 1
1 1 1 1
2 1 1 1
3 1 1 1
4 1 1 1
5 4 4 4
6 -1 -1 -1
7 -1 -1 -1
8 -1 -1 -1
9已选择10行。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货