• 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
    );
  • 相关阅读:
    EJB到底是什么,真的那么神秘吗??
    An Assembly Language
    Memory Layout (Virtual address space of a C process)
    手机测试用例-游戏测试用例
    手机测试用例-工具箱测试用例
    手机测试用例-输入法测试用例
    手机测试用例-时钟测试用例
    手机测试用例-多媒体测试用例
    手机测试用例-设置测试用例
    手机测试用例-通话记录测试用例
  • 原文地址:https://www.cnblogs.com/yanqb/p/16170707.html
Copyright © 2020-2023  润新知