使用MERGE INTO删除记录

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

最后的结果应该是正确的。