-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
);