SQL> SELECT T.*,quantity - quantity1 FROM
2 (SELECT NO,FIRST_VALUE(quantity) OVER(PARTITION BY NO ORDER BY DATE1 DESC) quantity,
3 FIRST_VALUE(date1) OVER(PARTITION BY NO ORDER BY DATE1 DESC) DATE1,
4 NO NO1,quantity quantity1,date1 DATE2
5 FROM TEST1) T
6 WHERE DATE1 <> DATE2
7 /NO QUANTITY DATE1 NO1 QUANTITY1 DATE2 QUANTITY-QUANTITY1
--- ---------- ---------- --- ---------- ---------- ------------------
B101 202 2005-07-09 B101 32 2005-07-06 170
B101 202 2005-07-09 B101 9 2005-07-04 193
B101 202 2005-07-09 B101 8 2005-07-01 194
B102 70 2005-07-08 B102 11 2005-07-05 59
B102 70 2005-07-08 B102 76 2005-07-03 -6
2 (SELECT NO,FIRST_VALUE(quantity) OVER(PARTITION BY NO ORDER BY DATE1 DESC) quantity,
3 FIRST_VALUE(date1) OVER(PARTITION BY NO ORDER BY DATE1 DESC) DATE1,
4 NO NO1,quantity quantity1,date1 DATE2
5 FROM TEST1) T
6 WHERE DATE1 <> DATE2
7 /NO QUANTITY DATE1 NO1 QUANTITY1 DATE2 QUANTITY-QUANTITY1
--- ---------- ---------- --- ---------- ---------- ------------------
B101 202 2005-07-09 B101 32 2005-07-06 170
B101 202 2005-07-09 B101 9 2005-07-04 193
B101 202 2005-07-09 B101 8 2005-07-01 194
B102 70 2005-07-08 B102 11 2005-07-05 59
B102 70 2005-07-08 B102 76 2005-07-03 -6
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货