MERGE INTO删除记录
在itpub上看了一帖子http://www.itpub.net/thread-1570535-1-1.html
需求：
SQL> SELECT * FROM tmp;
ID PROJECT PDATE CNT
---------- -------------------- -------------- ----------
1 pro1 06-2月 -12 2
1 pro1 07-2月 -12 10
1 pro1 08-2月 -12 10
1 pro1 09-2月 -12 20
2 pro2 10-2月 -12 5
2 pro2 11-2月 -12 5
已选择6行。
以tmp表查询结果为例，删除id与project相同的其它记录，只保存最早的那条数据 ，并且改变最早的那条记录的数量为id与project相同的所有记录的cnt之和，比如id=1,project='pro1'的所有记录cnt之和42。
SQL> SELECT * FROM tmp;
ID PROJECT PDATE CNT
---------- -------------------- -------------- ----------
1 pro1 06-2月 -12 2
1 pro1 07-2月 -12 10
1 pro1 08-2月 -12 10
1 pro1 09-2月 -12 20
2 pro2 10-2月 -12 5
2 pro2 11-2月 -12 5
已选择6行。
SQL> --以id,project分区并求和
SQL> SELECT ROWID rid, id,project,pdate,cnt,
2 SUM (cnt) OVER (PARTITION BY id, project) sum_qty from tmp ;
RID ID PROJECT PDATE CNT SUM_QTY
------------------ ---------- -------------------- -------------- ---------- ----------
AAANCZAAEAAAAH9AAG 1 pro1 06-2月 -12 2 42
AAANCZAAEAAAAH9AAH 1 pro1 07-2月 -12 10 42
AAANCZAAEAAAAH9AAI 1 pro1 08-2月 -12 10 42
AAANCZAAEAAAAH9AAJ 1 pro1 09-2月 -12 20 42
AAANCZAAEAAAAH9AAK 2 pro2 10-2月 -12 5 10
AAANCZAAEAAAAH9AAL 2 pro2 11-2月 -12 5 10
已选择6行。
SQL> --以id,project分区并求和，并 使用DENSE_RANK为记录编号
SQL> SELECT ROWID rid,project,pdate,cnt,
2 SUM (cnt) OVER (PARTITION BY id, project) sum_qty,
3 DENSE_RANK () OVER (PARTITION BY id, project ORDER BY pdate ) rn
4 FROM tmp;
RID PROJECT PDATE CNT SUM_QTY RN
------------------ -------------------- -------------- ---------- ---------- ----------
AAANCZAAEAAAAH9AAG pro1 06-2月 -12 2 42 1
AAANCZAAEAAAAH9AAH pro1 07-2月 -12 10 42 2
AAANCZAAEAAAAH9AAI pro1 08-2月 -12 10 42 3
AAANCZAAEAAAAH9AAJ pro1 09-2月 -12 20 42 4
AAANCZAAEAAAAH9AAK pro2 10-2月 -12 5 10 1
AAANCZAAEAAAAH9AAL pro2 11-2月 -12 5 10 2
已选择6行。
SQL> --使用merge into 更新并删除相关记录
SQL> MERGE INTO tmp t1
2 USING (SELECT ROWID rid,
3 project,
4 pdate,
5 cnt,
6 SUM (cnt) OVER (PARTITION BY id, project) sum_qty,
7 DENSE_RANK ()
8 OVER (PARTITION BY id, project ORDER BY pdate)
9 rn
10 FROM tmp) t2
11 ON (t1.ROWID = t2.rid)
12 WHEN MATCHED
13 THEN
14 UPDATE SET t1.cnt = sum_qty
15 DELETE
16 WHERE (t2.rn <> 1);
6 行已合并。
--显示了正确的结果
SQL> SELECT * FROM tmp;
ID PROJECT PDATE CNT
---------- -------------------- -------------- ----------
1 pro1 06-2月 -12 42
2 pro2 10-2月 -12 10
最后的结果应该是正确的。