MERGE INTO删除记录
以tmp表查询结果为例,删除id与project相同的其它记录,只保存最早的那条数据 ,并且改变最早的那条记录的数量为id与project相同的所有记录的cnt之和,比如id=1,project='pro1'的所有记录cnt之和42。
在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行。
- 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