select a.用户号,a.抄表日期 上次抄表日期,a.表数据 上次现抄,b.抄表日期 本次抄表日期,b.表数据 本次现抄,b.表数据-a.表数据 实际用量
from
(select (rank() over (order by 用户号,抄表日期,表数据)) rid,用户号,抄表日期,表数据 from tz) a,
(select (rank() over (order by 用户号,抄表日期,表数据)) rid,用户号,抄表日期,表数据 from tz) b
where a.用户号=b.用户号(+) and
a.rid=b.rid(+)-1;
from
(select (rank() over (order by 用户号,抄表日期,表数据)) rid,用户号,抄表日期,表数据 from tz) a,
(select (rank() over (order by 用户号,抄表日期,表数据)) rid,用户号,抄表日期,表数据 from tz) b
where a.用户号=b.用户号(+) and
a.rid=b.rid(+)-1;
---------- ---------- ----------
101 2004-01-15 100
102 2004-01-15 100
101 2004-02-15 200
102 2004-02-15 200
101 2004-03-15 300
102 2004-03-15 300
101 2004-04-15 400
102 2004-04-15 400
101 2004-05-15 500
102 2004-05-15 500已选择10行。已用时间: 00: 00: 00.15
10:27:57 SQL> select t.*,tt.抄表日期,tt.表数据,t.表数据-nvl(tt.表数据,0) sjyl from
10:28:01 2 (select tz.*,rank() over(partition by 用户号 order by 抄表日期) rk from tz) t,
10:28:01 3 (select tz.*,(rank() over(partition by 用户号 order by 抄表日期))+1 rk2 from tz) tt
10:28:01 4 where t.rk=tt.rk2(+) and t.用户号=tt.用户号(+);用户号 抄表日期 表数据 RK 抄表日期 表数据 SJYL
---------- ---------- ---------- ---------- ---------- ---------- ----------
101 2004-01-15 100 1 100
101 2004-02-15 200 2 2004-01-15 100 100
101 2004-03-15 300 3 2004-02-15 200 100
101 2004-04-15 400 4 2004-03-15 300 100
101 2004-05-15 500 5 2004-04-15 400 100
102 2004-01-15 100 1 100
102 2004-02-15 200 2 2004-01-15 100 100
102 2004-03-15 300 3 2004-02-15 200 100
102 2004-04-15 400 4 2004-03-15 300 100
102 2004-05-15 500 5 2004-04-15 400 100已选择10行。
SQL> select * from a1;BBB
----------
101
102
103
104
105SQL> select * from a2;BBB CCC
---------- --------------------
101
102
105SQL> select * from a1,a2 where a1.bbb(+)=a2.bbb;BBB BBB CCC
---------- ---------- --------------------
101 101
102 102
105 105SQL> select * from a1,a2 where a1.bbb=a2.bbb(+);BBB BBB CCC
---------- ---------- --------------------
101 101
102 102
103
104
105 105SQL>
10:40:48 2 select distinct 用户号,to_date('2003-12-15','yyyy-mm-dd'),0 from tz;已创建2行。已用时间: 00: 00: 00.31
10:41:26 SQL> select t.*,tt.抄表日期,tt.表数据,t.表数据-nvl(tt.表数据,0) sjyl from
10:42:29 2 (select tz.*,rank() over(partition by 用户号 order by 抄表日期) rk from tz) t,
10:42:29 3 (select tz.*,(rank() over(partition by 用户号 order by 抄表日期))+1 rk2 from tz) tt
10:42:29 4 where t.rk=tt.rk2(+) and t.用户号=tt.用户号(+) and t.rk>1;用户号 抄表日期 表数据 RK 抄表日期 表数据 SJYL
---------- ---------- ---------- ---------- ---------- ---------- ----------
101 2004-01-15 100 2 2003-12-15 0 100
101 2004-02-15 200 3 2004-01-15 100 100
101 2004-03-15 300 4 2004-02-15 200 100
101 2004-04-15 400 5 2004-03-15 300 100
101 2004-05-15 500 6 2004-04-15 400 100
102 2004-01-15 100 2 2003-12-15 0 100
102 2004-02-15 200 3 2004-01-15 100 100
102 2004-03-15 300 4 2004-02-15 200 100
102 2004-04-15 400 5 2004-03-15 300 100
102 2004-05-15 500 6 2004-04-15 400 100已选择10行。已用时间: 00: 00: 00.31
10:42:37 SQL>