SQL> select * from aa; ID NO QUANTITY MYDATE
---------- ---- ---------- ----------
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 no,
2 sum(decode(1,top_2,quantity,null)) quantity,
3 max(decode(1,top_2,mydate,null)) mydate,
4 max(no) no1,
5 sum(decode(2,top_2,quantity,null)) quantity1,
6 max(decode(2,top_2,mydate,null)) mydate1,
7 sum(decode(1,top_2,quantity,null))-sum(decode(2,top_2,quantity,null)) "quantity-quantity
1"
8 from (
9 SELECT no,quantity,mydate,row_number()
10 OVER (PARTITION BY no ORDER BY mydate desc NULLS LAST) top_2
11 FROM aa
12 ) b
13 where b.top_2<=2
14 group by no
15 having count(no)=2;NO QUANTITY MYDATE NO1 QUANTITY1 MYDATE1 quantity-quantity1
---- ---------- ---------- ---- ---------- ---------- ------------------
B101 202 09-7月 -05 B101 32 06-7月 -05 170
B102 70 08-7月 -05 B102 11 05-7月 -05 59
---------- ---- ---------- ----------
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 no,
2 sum(decode(1,top_2,quantity,null)) quantity,
3 max(decode(1,top_2,mydate,null)) mydate,
4 max(no) no1,
5 sum(decode(2,top_2,quantity,null)) quantity1,
6 max(decode(2,top_2,mydate,null)) mydate1,
7 sum(decode(1,top_2,quantity,null))-sum(decode(2,top_2,quantity,null)) "quantity-quantity
1"
8 from (
9 SELECT no,quantity,mydate,row_number()
10 OVER (PARTITION BY no ORDER BY mydate desc NULLS LAST) top_2
11 FROM aa
12 ) b
13 where b.top_2<=2
14 group by no
15 having count(no)=2;NO QUANTITY MYDATE NO1 QUANTITY1 MYDATE1 quantity-quantity1
---- ---------- ---------- ---- ---------- ---------- ------------------
B101 202 09-7月 -05 B101 32 06-7月 -05 170
B102 70 08-7月 -05 B102 11 05-7月 -05 59
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货