Oracle改造mysql关于MERGE INTO用法

-Oracle改造Mysql记录；关于MERGE INTO问题

由于项目中有个定时任务，每十分钟执行一次；组织node_id大概4000多条，遍历查询统计数据，（每次更新数据量大）

所有用到一张临时表，先记录已经有的数据，然后根据判断更新或插入mysql 的REPLACE INTO  其实更好用

 

 

<!--Job排查率统计 组织数据-->
<update id="checkItemGroupAll" parameterType="java.util.Map" >
        REPLACE INTO SRP_TR_CHECKITEMSTATEGROUP_ALL
        ( NODE_ID, PLANENDDT, STATE01, STATE02, STATE03, STATE04, STATE05, STATE06, STATE07, STATE08, TOTAL, UPDATE_DT,
          DAYCOUNT, WEEKCOUNT, MONTHCOUNT, SEASONCOUNT, YEARCOUNT, DAYCHECKEDCOUNT, WEEKCHECKEDCOUNT, MONTHCHECKEDCOUNT, SEASONCHECKEDCOUNT, YEARCHECKEDCOUNT, TENDAYSCOUNT, TENDAYSCHECKEDCOUNT, UNIT_TOTAL
        )
         SELECT O.NODE_ID NODE_ID,
                T.PLANENDDT,
                SUM(STATE01) STATE01,
                SUM(STATE02) STATE02,
                SUM(STATE03) STATE03,
                SUM(STATE04) STATE04,
                SUM(STATE05) STATE05,
                SUM(STATE06) STATE06,
                SUM(STATE07) STATE07,
                SUM(STATE08) STATE08,
                SUM(TOTAL) TOTAL,
                now() UPDATE_DT,
                -- 以下小立加
                SUM(DAYCOUNT)               DAYCOUNT,
                SUM(WEEKCOUNT)              WEEKCOUNT,
                SUM(MONTHCOUNT)             MONTHCOUNT,
                SUM(SEASONCOUNT)            SEASONCOUNT,
                SUM(YEARCOUNT)              YEARCOUNT,
                SUM(TENDAYSCOUNT)           TENDAYSCOUNT,
                SUM(DAYCHECKEDCOUNT)        DAYCHECKEDCOUNT,
                SUM(WEEKCHECKEDCOUNT)       WEEKCHECKEDCOUNT,
                SUM(MONTHCHECKEDCOUNT)      MONTHCHECKEDCOUNT,
                SUM(SEASONCHECKEDCOUNT)     SEASONCHECKEDCOUNT,
                SUM(YEARCHECKEDCOUNT)       YEARCHECKEDCOUNT,
                SUM(TENDAYSCHECKEDCOUNT)    TENDAYSCHECKEDCOUNT,
                SUM(UNIT_TOTAL)             UNIT_TOTAL
         FROM SRP_OM_ORGNIZATION O
         WHERE  O.NODE_ID NOT LIKE '%-00' AND T.PLANENDDT IS NOT NULL
         GROUP BY T.PLANENDDT
</update>

下面是Oracle中的 MERGE INTO 区别还是很大的。

MERGE INTO SRP_TR_CHECKITEMSTATEGROUP_ALL AA
    USING (SELECT CUR.NODE_ID NODE_ID,
                  T.PLANENDDT,
                  SUM(STATE01) STATE01,
                  SUM(STATE02) STATE02,
                  SUM(STATE03) STATE03,
                  SUM(STATE04) STATE04,
                  SUM(STATE05) STATE05,
                  SUM(STATE06) STATE06,
                  SUM(STATE07) STATE07,
                  SUM(STATE08) STATE08,
                  SUM(TOTAL) TOTAL,
                  now() UPDATE_DT,
                  -- 以下小立加
                  SUM(DAYCOUNT)               DAYCOUNT,
                  SUM(WEEKCOUNT)              WEEKCOUNT,
                  SUM(MONTHCOUNT)             MONTHCOUNT,
                  SUM(SEASONCOUNT)            SEASONCOUNT,
                  SUM(YEARCOUNT)              YEARCOUNT,
                  SUM(TENDAYSCOUNT)           TENDAYSCOUNT,
                  SUM(DAYCHECKEDCOUNT)        DAYCHECKEDCOUNT,
                  SUM(WEEKCHECKEDCOUNT)       WEEKCHECKEDCOUNT,
                  SUM(MONTHCHECKEDCOUNT)      MONTHCHECKEDCOUNT,
                  SUM(SEASONCHECKEDCOUNT)     SEASONCHECKEDCOUNT,
                  SUM(YEARCHECKEDCOUNT)       YEARCHECKEDCOUNT,
                  SUM(TENDAYSCHECKEDCOUNT)    TENDAYSCHECKEDCOUNT,
                  SUM(UNIT_TOTAL)             UNIT_TOTAL
           FROM SRP_TR_CHECKITEM_STATE_G_TEMP T
           WHERE T.NODE_ID LIKE CONCAT(CUR.NODE_ID, '%')
           GROUP BY T.PLANENDDT) BB
    ON (AA.NODE_ID = BB.NODE_ID AND AA.PLANENDDT = BB.PLANENDDT) -- 关联条件
    WHEN MATCHED THEN
        UPDATE
            SET AA.STATE01     = BB.STATE01,
                AA.STATE02     = BB.STATE02,
                AA.STATE03     = BB.STATE03,
                AA.STATE04     = BB.STATE04,
                AA.STATE05     = BB.STATE05,
                AA.STATE06     = BB.STATE06,
                AA.STATE07     = BB.STATE07,
                AA.STATE08     = BB.STATE08,
                AA.TOTAL       = BB.TOTAL,
                AA.UPDATE_DT   = SYSDATE,
                AA.DAYCOUNT           = BB.DAYCOUNT,
                AA.WEEKCOUNT          = BB.WEEKCOUNT,
                AA.MONTHCOUNT         = BB.MONTHCOUNT,
                AA.SEASONCOUNT        = BB.SEASONCOUNT,
                AA.YEARCOUNT          = BB.YEARCOUNT,
                AA.DAYCHECKEDCOUNT    = BB.DAYCHECKEDCOUNT,
                AA.TENDAYSCOUNT       = BB.TENDAYSCOUNT,
                AA.WEEKCHECKEDCOUNT   = BB.WEEKCHECKEDCOUNT,
                AA.MONTHCHECKEDCOUNT  = BB.MONTHCHECKEDCOUNT,
                AA.SEASONCHECKEDCOUNT = BB.SEASONCHECKEDCOUNT,
                AA.YEARCHECKEDCOUNT   = BB.YEARCHECKEDCOUNT,
                AA.TENDAYSCHECKEDCOUNT= BB.TENDAYSCHECKEDCOUNT,
                AA.UNIT_TOTAL         = BB.UNIT_TOTAL
    WHEN NOT MATCHED THEN
        INSERT
            ( NODE_ID, PLANENDDT, STATE01, STATE02, STATE03, STATE04, STATE05, STATE06, STATE07, STATE08, TOTAL, UPDATE_DT,
              DAYCOUNT, WEEKCOUNT, MONTHCOUNT, SEASONCOUNT, YEARCOUNT, DAYCHECKEDCOUNT, WEEKCHECKEDCOUNT, MONTHCHECKEDCOUNT, SEASONCHECKEDCOUNT, YEARCHECKEDCOUNT, TENDAYSCOUNT, TENDAYSCHECKEDCOUNT, UNIT_TOTAL
                )
            VALUES  (BB.NODE_ID, BB.PLANENDDT, BB.STATE01, BB.STATE02, BB.STATE03, BB.STATE04, BB.STATE05, BB.STATE06, BB.STATE07, BB.STATE08, BB.TOTAL, SYSDATE,
    BB.DAYCOUNT, BB.WEEKCOUNT, BB.MONTHCOUNT, BB.SEASONCOUNT, BB.YEARCOUNT, BB.DAYCHECKEDCOUNT, BB.WEEKCHECKEDCOUNT, BB.MONTHCHECKEDCOUNT, BB.SEASONCHECKEDCOUNT, BB.YEARCHECKEDCOUNT, BB.TENDAYSCOUNT, BB.TENDAYSCHECKEDCOUNT, BB.UNIT_TOTAL
  );