SQL> select * from test
2 /ID NO QUANTITY NEWDATE
---------- ---------- ---------- ----------
1 B101 8 01-7月 -05
2 B003 89 01-7月 -05
3 B102 76 03-7月 -05
4 B101 9 04-7月 -05
5 B101 32 06-7月 -05
6 B102 11 05-7月 -05
7 B102 70 08-7月 -05
8 B101 202 09-7月 -05已选择8行。SQL> select t.*,quantity - quantity1 from
2 (select NO,quantity,newdate,
3 lead(NO) over(partition by NO order by newdate desc) NO1,
4 lead(quantity) over(partition by NO order by newdate desc) quantity1
5 from test) t
6 where NO1 is not null
7 /NO QUANTITY NEWDATE NO1 QUANTITY1 QUANTITY-QUANTITY1
---------- ---------- ---------- ---------- ---------- ------------------
B101 202 09-7月 -05 B101 32 170
B101 32 06-7月 -05 B101 9 23
B101 9 04-7月 -05 B101 8 1
B102 70 08-7月 -05 B102 11 59
B102 11 05-7月 -05 B102 76 -65
2 /ID NO QUANTITY NEWDATE
---------- ---------- ---------- ----------
1 B101 8 01-7月 -05
2 B003 89 01-7月 -05
3 B102 76 03-7月 -05
4 B101 9 04-7月 -05
5 B101 32 06-7月 -05
6 B102 11 05-7月 -05
7 B102 70 08-7月 -05
8 B101 202 09-7月 -05已选择8行。SQL> select t.*,quantity - quantity1 from
2 (select NO,quantity,newdate,
3 lead(NO) over(partition by NO order by newdate desc) NO1,
4 lead(quantity) over(partition by NO order by newdate desc) quantity1
5 from test) t
6 where NO1 is not null
7 /NO QUANTITY NEWDATE NO1 QUANTITY1 QUANTITY-QUANTITY1
---------- ---------- ---------- ---------- ---------- ------------------
B101 202 09-7月 -05 B101 32 170
B101 32 06-7月 -05 B101 9 23
B101 9 04-7月 -05 B101 8 1
B102 70 08-7月 -05 B102 11 59
B102 11 05-7月 -05 B102 76 -65
A.quantity as quantity,
A.date as date,
A.No as No1,
B.quantity as quantity1,
B.date as date1
(A.quantity-B.quantity) as differ
from test A,
test B
where A.No = B.No
and B.date = (select max(date) from test where No=A.No and date < A.date)
order A.date dec