首页 > 代码库 > 纪念我人生中第一个merge into语句
纪念我人生中第一个merge into语句
做按组织关系汇总功能时，当数据量特别大，或者汇总组织特别多时，运行效率特别低，于是使用了merge into语句。
代码如下：
public void updateInsertData(DataSet dsl, String mxnm, DataSet dsc, DataSet dsr, String faslnm, DataSet dyxjDs, String thisBbnm, String tabname, DataSet glDs,String flnm,String dyzzNm,int hzbbStat){ //using后的sql String queryDataSql = queryData(dsl, mxnm, dsc, dsr, faslnm, dyxjDs, thisBbnm, tabname, glDs); StringBuffer updateInsertSql = new StringBuffer(); SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss "); String createdTime = formatter.format(new Date()); String lastmodifiedTime = formatter.format(new Date()); String creator = GetBspInfo.getBspInfo().getUserId(); updateInsertSql.append("MERGE INTO FCDATA_"+mxnm +" DA "); updateInsertSql.append("USING ("+queryDataSql + ") QU "); updateInsertSql.append("ON ("); for (int y = 0; y < dsl.getCount(); y++) { String wdmc = (String) dsl.getRecord(y).get("FCYSYS_BS"); if(!("ZZ".equals(wdmc))) { updateInsertSql.append("DA.FC_" + wdmc + "_NM = QU.FC_" + wdmc + "_NM AND "); } } updateInsertSql.append(" DA.FC_ZZ_NM=‘" +dyzzNm+"‘ )"); updateInsertSql.append("WHEN MATCHED THEN "); updateInsertSql.append("UPDATE SET DA.FC_QJS = ROUND(QU.FC_QJS,QU.FCBZL_DECN),"); updateInsertSql.append("DA.FC_LJS = ROUND(QU.FC_LJS,QU.FCBZL_DECN),DA.FC_NDS = ROUND(QU.FC_NDS,QU.FCBZL_DECN),"); updateInsertSql.append("DA.FC_LJS_C = ROUND(QU.FC_LJS_C,QU.FCBZL_DECN),DA.FC_NDS_C = ROUND(QU.FC_NDS_C,QU.FCBZL_DECN),"); updateInsertSql.append("DA.FC_QJS_C = ROUND(QU.FC_QJS_C,QU.FCBZL_DECN),DA.FC_NDS_1 = ROUND(QU.FC_NDS_1,QU.FCBZL_DECN),"); updateInsertSql.append("DA.FC_LJS_1 = ROUND(QU.FC_LJS_1,QU.FCBZL_DECN),DA.FC_QJS_1 = ROUND(QU.FC_QJS_1,QU.FCBZL_DECN),"); updateInsertSql.append("FC_SJLY=‘2‘,FC_HZLBNM=‘" + flnm + "‘,LASTMODIFIER=‘" + creator + "‘,LASTMODIFIEDTIME="); updateInsertSql.append("(select to_date(‘").append(lastmodifiedTime).append("‘,‘YYYY-MM-DD HH24:MI:SS‘)from dual) "); updateInsertSql.append("WHEN NOT MATCHED THEN INSERT("); updateInsertSql.append("FC_ZZ_NM,FC_MXNM,FC_DANM,FC_BBDY,FC_LCST,"); for (int w = 0; w < dsl.getCount(); w++) { String wdmc = (String) dsl.getRecord(w).get("FCYSYS_BS"); if (!("ZZ".equals(wdmc))) { updateInsertSql.append("FC_" + wdmc + "_NM,"); } } updateInsertSql.append("FC_QJS,FC_NDS,FC_LJS,FC_QJS_C,FC_NDS_C,FC_LJS_C,FC_QJS_1,FC_NDS_1,FC_LJS_1,"); updateInsertSql.append("FC_SJLY,FC_HZLBNM,CREATOR,LASTMODIFIER,CREATEDTIME,LASTMODIFIEDTIME) "); updateInsertSql.append(" VALUES( ‘" + dyzzNm + "‘,‘" + mxnm + "‘,substr(sys_guid(),0,4)||sys_guid(),‘" + thisBbnm + "‘,‘"); updateInsertSql.append(hzbbStat + "‘,"); for (int w = 0; w < dsl.getCount(); w++) { String wdmc = (String) dsl.getRecord(w).get("FCYSYS_BS"); if (!("ZZ".equals(wdmc))) { updateInsertSql.append("QU.FC_" + wdmc + "_NM,"); } } updateInsertSql.append("ROUND(QU.FC_QJS,QU.FCBZL_DECN),ROUND(QU.FC_NDS,QU.FCBZL_DECN),ROUND(QU.FC_LJS,QU.FCBZL_DECN),"); updateInsertSql.append("ROUND(QU.FC_QJS_C,QU.FCBZL_DECN),ROUND(QU.FC_NDS_C,QU.FCBZL_DECN),ROUND(QU.FC_LJS_C,QU.FCBZL_DECN),"); updateInsertSql.append("ROUND(QU.FC_QJS_1,QU.FCBZL_DECN),ROUND(QU.FC_NDS_1,QU.FCBZL_DECN),ROUND(QU.FC_LJS_1,QU.FCBZL_DECN),‘"); updateInsertSql.append("2‘,‘" + flnm + "‘,‘"); updateInsertSql.append(creator + "‘,‘" + creator + "‘,"); updateInsertSql.append("(select to_date(‘").append(createdTime).append("‘,‘YYYY-MM-DD HH24:MI:SS‘)from dual),"); updateInsertSql.append("(select to_date(‘").append(lastmodifiedTime).append("‘,‘YYYY-MM-DD HH24:MI:SS‘)from dual) )"); this.executeUpdate(updateInsertSql.toString()); /********************merge into结束！！！！*************************/ }
public String queryData(DataSet dsl, String mxnm, DataSet dsc, DataSet dsr, String faslnm, DataSet dyxjDs, String thisBbnm, String tabname, DataSet glDs) { boolean ifzb = false;//数据列中是否存在指标这个维度 for (int s = 0; s < dsc.getCount(); s++) { String sjmc = (String) dsc.getRecord(s).get("FCYSYS_BS"); if (!("ZB".equals(sjmc))) { ifzb = false; } else { ifzb = true; break; } } //查询出下级组织报表的所有单元格数据，便于下面判断该数据是否在上级组织中有记录 StringBuffer sqlh = new StringBuffer(200); sqlh.append(" SELECT FCBZL_DECN,"); for (int w = 0; w < dsl.getCount(); w++) { String wdmc = (String) dsl.getRecord(w).get("FCYSYS_BS"); if (!("ZZ".equals(wdmc))) { sqlh.append("FC_" + wdmc + "_NM,"); } } if (ifzb) { sqlh.append(" CASE WHEN YSYSXM_XMXZ=‘2‘ OR YSYSXM_XMXZ=‘3‘ THEN MAX(FC_QJS)"); sqlh.append(" ELSE SUM(NVL(FC_QJS, 0) * FCFLYS_BL) END AS FC_QJS, "); sqlh.append(" CASE WHEN YSYSXM_XMXZ=‘2‘ OR YSYSXM_XMXZ=‘3‘ THEN MAX(FC_NDS)"); sqlh.append(" ELSE SUM(NVL(FC_NDS, 0) * FCFLYS_BL) END AS FC_NDS, "); sqlh.append(" CASE WHEN YSYSXM_XMXZ=‘2‘ OR YSYSXM_XMXZ=‘3‘ THEN MAX(FC_LJS)"); sqlh.append(" ELSE SUM(NVL(FC_LJS, 0) * FCFLYS_BL) END AS FC_LJS, "); sqlh.append(" CASE WHEN YSYSXM_XMXZ=‘2‘ OR YSYSXM_XMXZ=‘3‘ THEN MAX(FC_QJS_C)"); sqlh.append(" ELSE SUM(NVL(FC_QJS_C, 0) * FCFLYS_BL) END AS FC_QJS_C, "); sqlh.append(" CASE WHEN YSYSXM_XMXZ=‘2‘ OR YSYSXM_XMXZ=‘3‘ THEN MAX(FC_NDS_C)"); sqlh.append(" ELSE SUM(NVL(FC_NDS_C, 0) * FCFLYS_BL) END AS FC_NDS_C, "); sqlh.append(" CASE WHEN YSYSXM_XMXZ=‘2‘ OR YSYSXM_XMXZ=‘3‘ THEN MAX(FC_LJS_C)"); sqlh.append(" ELSE SUM(NVL(FC_LJS_C, 0) * FCFLYS_BL) END AS FC_LJS_C, "); sqlh.append(" CASE WHEN YSYSXM_XMXZ=‘2‘ OR YSYSXM_XMXZ=‘3‘ THEN MAX(FC_QJS_1)"); sqlh.append(" ELSE SUM(NVL(FC_QJS_1, 0) * FCFLYS_BL) END AS FC_QJS_1, "); sqlh.append(" CASE WHEN YSYSXM_XMXZ=‘2‘ OR YSYSXM_XMXZ=‘3‘ THEN MAX(FC_NDS_1)"); sqlh.append(" ELSE SUM(NVL(FC_NDS_1, 0) * FCFLYS_BL) END AS FC_NDS_1, "); sqlh.append(" CASE WHEN YSYSXM_XMXZ=‘2‘ OR YSYSXM_XMXZ=‘3‘ THEN MAX(FC_LJS_1)"); sqlh.append(" ELSE SUM(NVL(FC_LJS_1, 0) * FCFLYS_BL) END AS FC_LJS_1"); } else { sqlh.append("SUM(NVL(FC_QJS,0)*FCFLYS_BL) AS FC_QJS,SUM(NVL(FC_NDS,0)*FCFLYS_BL) AS FC_NDS,SUM(NVL(FC_LJS,0)*FCFLYS_BL) AS FC_LJS,"); sqlh.append("SUM(NVL(FC_QJS_1,0)*FCFLYS_BL) AS FC_QJS_1,SUM(NVL(FC_NDS_1,0)*FCFLYS_BL) AS FC_NDS_1,"); sqlh.append("SUM(NVL(FC_LJS_1,0)*FCFLYS_BL) AS FC_LJS_1,SUM(NVL(FC_QJS_C,0)*FCFLYS_BL) AS FC_QJS_C,SUM(NVL(FC_NDS_C,0)*FCFLYS_BL) AS FC_NDS_C,SUM(NVL(FC_LJS_C,0)*FCFLYS_BL) AS FC_LJS_C"); } sqlh.append(" FROM FCDATA_" + mxnm); /*本来是join fcbzh,但是后来调整为只汇总固定行，而fcirow里存的都是固定行，所以改为join fcirow*/ sqlh.append(" JOIN FCBZH_" + mxnm + " ON "); //sqlh.append(" JOIN FCIROW ON "); for (int x = 0; x < dsr.getCount(); x++) { String xxmc = (String) dsr.getRecord(x).get("FCYSYS_BS"); if (!("ZZ".equals(xxmc))) { sqlh.append(" FC_" + xxmc + "_NM = FCBZH_" + xxmc + "NM AND "); //sqlh.append(" FC_" + xxmc + "_NM = FCIROW_" + xxmc + " AND "); } } sqlh.append(" FC_ZZ_NM = FCBZH_ZZNM AND "); sqlh.append(" 1=1 JOIN FCBZL_" + mxnm + " ON "); for (int s = 0; s < dsc.getCount(); s++) { String sjmc = (String) dsc.getRecord(s).get("FCYSYS_BS"); if (!("ZB".equals(sjmc))) { sqlh.append(" FC_" + sjmc + "_NM = FCBZL_" + sjmc + " AND "); } else { sqlh.append(" FC_" + sjmc + "_NM = FCBZL_" + sjmc + " AND "); } } sqlh.append(" 1=1 "); //指标这个维度数据结构不同，单独处理 if (ifzb) { sqlh.append(" JOIN YSYSXM ON YSYSXM_XMUID = FC_ZB_NM "); } sqlh.append(" JOIN " + tabname + " ON FCFLYS_SJNM = FC_ZZ_NM "); /*sqlh.append(" JOIN FCBBRW ON FCBBRW_ZZ=FC_ZZ_NM WHERE FC_BBDY = ‘" + thisBbnm + "‘ ");*/ //两张表同一个模型内码，但有一个归口表 sqlh.append(" JOIN FCBBRW ON FCBBRW_ZZ=FC_ZZ_NM AND FCBBRW_BBNM=FC_BBDY WHERE 1=1"); /*sqlh.append(" JOIN FCBBRW ON FCBBRW_ZZ=FC_ZZ_NM WHERE 1=1");*/ sqlh.append(" AND FCBZL_IFBL=‘0‘ AND FCBZH_SLNM=‘" + faslnm + "‘ AND FCBZL_SLNM=‘" + faslnm + "‘ AND FCBZL_DYNM=‘" + thisBbnm + "‘ AND FCBZH_DYNM=‘" + thisBbnm + "‘"); sqlh.append(" AND FCBBRW_SLNM=‘" + faslnm + "‘ AND FCBBRW_BBNM =‘" + thisBbnm + "‘ "); if (glDs.getCount() > 2) { sqlh.append(" AND FCBBRW_IFGL=‘1‘ "); } sqlh.append(" AND FC_ZZ_NM IN ("); //下级组织范围条件 for (int zz = 0; zz < dyxjDs.getCount(); zz++) { String xjzznm = (String) dyxjDs.getRecord(zz).get("ZZNM"); if (zz == dyxjDs.getCount() - 1) { sqlh.append("‘" + xjzznm + "‘)"); } else { sqlh.append("‘" + xjzznm + "‘,"); } } sqlh.append(" AND FCBZH_ZZNM IN ("); //下级组织范围条件 for (int zz = 0; zz < dyxjDs.getCount(); zz++) { String xjzznm = (String) dyxjDs.getRecord(zz).get("ZZNM"); if (zz == dyxjDs.getCount() - 1) { sqlh.append("‘" + xjzznm + "‘)"); } else { sqlh.append("‘" + xjzznm + "‘,"); } } sqlh.append(" GROUP BY "); if (ifzb) { sqlh.append(" YSYSXM_XMXZ,"); } for (int y = 0; y < dsl.getCount(); y++) { String wdmc = (String) dsl.getRecord(y).get("FCYSYS_BS"); if (!("ZZ".equals(wdmc))) { sqlh.append("FC_" + wdmc + "_NM,"); } } sqlh.append("FCBZL_DECN"); return sqlh.toString(); }
运行处的sql语句如下：
MERGE INTO FCDATA_XS04 DAUSING (SELECT FCBZL_DECN, FC_QYXM_NM, FC_MB_NM, FC_ZB_NM, FC_LX_NM, FC_QJ_NM, CASE WHEN YSYSXM_XMXZ = ‘2‘ OR YSYSXM_XMXZ = ‘3‘ THEN MAX(FC_QJS) ELSE SUM(NVL(FC_QJS, 0) * FCFLYS_BL) END AS FC_QJS, CASE WHEN YSYSXM_XMXZ = ‘2‘ OR YSYSXM_XMXZ = ‘3‘ THEN MAX(FC_NDS) ELSE SUM(NVL(FC_NDS, 0) * FCFLYS_BL) END AS FC_NDS, CASE WHEN YSYSXM_XMXZ = ‘2‘ OR YSYSXM_XMXZ = ‘3‘ THEN MAX(FC_LJS) ELSE SUM(NVL(FC_LJS, 0) * FCFLYS_BL) END AS FC_LJS, CASE WHEN YSYSXM_XMXZ = ‘2‘ OR YSYSXM_XMXZ = ‘3‘ THEN MAX(FC_QJS_C) ELSE SUM(NVL(FC_QJS_C, 0) * FCFLYS_BL) END AS FC_QJS_C, CASE WHEN YSYSXM_XMXZ = ‘2‘ OR YSYSXM_XMXZ = ‘3‘ THEN MAX(FC_NDS_C) ELSE SUM(NVL(FC_NDS_C, 0) * FCFLYS_BL) END AS FC_NDS_C, CASE WHEN YSYSXM_XMXZ = ‘2‘ OR YSYSXM_XMXZ = ‘3‘ THEN MAX(FC_LJS_C) ELSE SUM(NVL(FC_LJS_C, 0) * FCFLYS_BL) END AS FC_LJS_C, CASE WHEN YSYSXM_XMXZ = ‘2‘ OR YSYSXM_XMXZ = ‘3‘ THEN MAX(FC_QJS_1) ELSE SUM(NVL(FC_QJS_1, 0) * FCFLYS_BL) END AS FC_QJS_1, CASE WHEN YSYSXM_XMXZ = ‘2‘ OR YSYSXM_XMXZ = ‘3‘ THEN MAX(FC_NDS_1) ELSE SUM(NVL(FC_NDS_1, 0) * FCFLYS_BL) END AS FC_NDS_1, CASE WHEN YSYSXM_XMXZ = ‘2‘ OR YSYSXM_XMXZ = ‘3‘ THEN MAX(FC_LJS_1) ELSE SUM(NVL(FC_LJS_1, 0) * FCFLYS_BL) END AS FC_LJS_1 FROM FCDATA_XS04 JOIN FCBZH_XS04 ON FC_QYXM_NM = FCBZH_QYXMNM AND FC_ZZ_NM = FCBZH_ZZNM AND 1 = 1 JOIN FCBZL_XS04 ON FC_MB_NM = FCBZL_MB AND FC_QJ_NM = FCBZL_QJ AND FC_ZB_NM = FCBZL_ZB AND FC_LX_NM = FCBZL_LX AND 1 = 1 JOIN YSYSXM ON YSYSXM_XMUID = FC_ZB_NM JOIN FCFLYS_ZZHZ ON FCFLYS_SJNM = FC_ZZ_NM JOIN FCBBRW ON FCBBRW_ZZ = FC_ZZ_NM AND FCBBRW_BBNM = FC_BBDY WHERE 1 = 1 AND FCBZL_IFBL = ‘0‘ AND FCBZH_SLNM = ‘f4f10134-ca32-4329-88fa-1e2e5ab5c47e‘ AND FCBZL_SLNM = ‘f4f10134-ca32-4329-88fa-1e2e5ab5c47e‘ AND FCBZL_DYNM = ‘262ff333-8e40-47ec-8753-b18a4fac9331‘ AND FCBZH_DYNM = ‘262ff333-8e40-47ec-8753-b18a4fac9331‘ AND FCBBRW_SLNM = ‘f4f10134-ca32-4329-88fa-1e2e5ab5c47e‘ AND FCBBRW_BBNM = ‘262ff333-8e40-47ec-8753-b18a4fac9331‘ AND FC_ZZ_NM IN (‘a0f8f16c-fa4e-4bf5-bb74-d9b5f9ac8640‘, ‘1c42551c-3749-48ec-b2a5-7cdac85f3915‘) AND FCBZH_ZZNM IN (‘a0f8f16c-fa4e-4bf5-bb74-d9b5f9ac8640‘, ‘1c42551c-3749-48ec-b2a5-7cdac85f3915‘) GROUP BY YSYSXM_XMXZ, FC_QYXM_NM, FC_MB_NM, FC_ZB_NM, FC_LX_NM, FC_QJ_NM, FCBZL_DECN) QUON (DA.FC_QYXM_NM = QU.FC_QYXM_NM AND DA.FC_MB_NM = QU.FC_MB_NM AND DA.FC_ZB_NM = QU.FC_ZB_NM AND DA.FC_LX_NM = QU.FC_LX_NM AND DA.FC_QJ_NM = QU.FC_QJ_NM AND DA.FC_ZZ_NM = ‘f9b6a300-534c-49f0-9aca-63b5a4caf8d8‘)WHEN MATCHED THEN UPDATE SET DA.FC_QJS = ROUND(QU.FC_QJS, QU.FCBZL_DECN), DA.FC_LJS = ROUND(QU.FC_LJS, QU.FCBZL_DECN), DA.FC_NDS = ROUND(QU.FC_NDS, QU.FCBZL_DECN), DA.FC_LJS_C = ROUND(QU.FC_LJS_C, QU.FCBZL_DECN), DA.FC_NDS_C = ROUND(QU.FC_NDS_C, QU.FCBZL_DECN), DA.FC_QJS_C = ROUND(QU.FC_QJS_C, QU.FCBZL_DECN), DA.FC_NDS_1 = ROUND(QU.FC_NDS_1, QU.FCBZL_DECN), DA.FC_LJS_1 = ROUND(QU.FC_LJS_1, QU.FCBZL_DECN), DA.FC_QJS_1 = ROUND(QU.FC_QJS_1, QU.FCBZL_DECN), FC_SJLY = ‘2‘, FC_HZLBNM = ‘fc302219-8a8b-484a-a9ff-2292a19606e3‘, LASTMODIFIER = ‘LYX‘, LASTMODIFIEDTIME = (select to_date(‘2017-07-21 14:06:01 ‘, ‘YYYY-MM-DD HH24:MI:SS‘) from dual)WHEN NOT MATCHED THEN INSERT (FC_ZZ_NM, FC_MXNM, FC_DANM, FC_BBDY, FC_LCST, FC_QYXM_NM, FC_MB_NM, FC_ZB_NM, FC_LX_NM, FC_QJ_NM, FC_QJS, FC_NDS, FC_LJS, FC_QJS_C, FC_NDS_C, FC_LJS_C, FC_QJS_1, FC_NDS_1, FC_LJS_1, FC_SJLY, FC_HZLBNM, CREATOR, LASTMODIFIER, CREATEDTIME, LASTMODIFIEDTIME) VALUES (‘f9b6a300-534c-49f0-9aca-63b5a4caf8d8‘, ‘XS04‘, substr(sys_guid(), 0, 4) || sys_guid(), ‘262ff333-8e40-47ec-8753-b18a4fac9331‘, ‘1‘, QU.FC_QYXM_NM, QU.FC_MB_NM, QU.FC_ZB_NM, QU.FC_LX_NM, QU.FC_QJ_NM, ROUND(QU.FC_QJS, QU.FCBZL_DECN), ROUND(QU.FC_NDS, QU.FCBZL_DECN), ROUND(QU.FC_LJS, QU.FCBZL_DECN), ROUND(QU.FC_QJS_C, QU.FCBZL_DECN), ROUND(QU.FC_NDS_C, QU.FCBZL_DECN), ROUND(QU.FC_LJS_C, QU.FCBZL_DECN), ROUND(QU.FC_QJS_1, QU.FCBZL_DECN), ROUND(QU.FC_NDS_1, QU.FCBZL_DECN), ROUND(QU.FC_LJS_1, QU.FCBZL_DECN), ‘2‘, ‘fc302219-8a8b-484a-a9ff-2292a19606e3‘, ‘LYX‘, ‘LYX‘, (select to_date(‘2017-07-21 14:06:01 ‘, ‘YYYY-MM-DD HH24:MI:SS‘) from dual), (select to_date(‘2017-07-21 14:06:01 ‘, ‘YYYY-MM-DD HH24:MI:SS‘) from dual))
其实merge into语句的语法概括起来如下：
MERGE INTO table_name t1
USING (table|view|sub_query) t2
ON (join condition)
WHEN MATCHED THEN
UPDATE table_name
SET col1 = col_val1, col2 = col_val2
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);
纪念我人生中第一个merge into语句
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。