select  *    from ( -- 查询基本信息    select    term.TERMINAL_ID BASIC_TERM_ID,        max(term.APP_TERM_NO) APP_TERM_NO,        max(device.DEVICE_TYPE) DEVICE_TYPE,        max(model.MODEL_DESC) MODEL_DESC,        max(branch.SHORT_NAME) branch_name,        max(subbranch.SHORT_NAME) sub_name,        max(self.SHORT_NAME) self_name,        max(self.INST_TYPE) INST_TYPE    from     SELFCUR.OPS_TERMINAL_INFO    term,        SELFCUR.OPS_DEVICE_INFO        device,        SELFCUR.OPS_DEVICE_MODEL    model,        SELFCUR.OPS_INSTITUTION        branch,        SELFCUR.OPS_INSTITUTION        subbranch,        SELFCUR.OPS_INSTITUTION        self,        SELFCUR.BIZ_MAIN_TRANS_HIS    trans    where    trans.TERM_ID=term.TERMINAL_ID         and term.TERMINAL_ID=device.TERMINAL_ID            and device.MODEL_ID=model.MODEL_ID            and term.INST_ID=self.INST_ID            and self.PARENT_INST_ID=subbranch.INST_ID            and subbranch.PARENT_INST_ID=branch.INST_ID        -- 这里需要加入特殊符号来标识，用界面传过来的参数组成sql进行替换        --XXXXYYYY--    group by term.TERMINAL_ID   )trans_basicleft join( -- 联通缴费    select trans.TERM_ID  TERM_ID,               count(1) PAY_LT_Count,               sum(trans.TRAN_AMT) PAY_LT_Money    from   SELFCUR.BIZ_MAIN_TRANS_HIS trans        where  trans.P_TRANS_CODE=‘1011402‘               and BIZ_ID=‘009‘    group by trans.TERM_ID        ) trans_pay_lton     trans_basic.BASIC_TERM_ID=trans_pay_lt.TERM_IDleft join( -- 移动缴费    select trans.TERM_ID  TERM_ID,               count(1) PAY_YD_Count,               sum(trans.TRAN_AMT) PAY_YD_Money    from   SELFCUR.BIZ_MAIN_TRANS_HIS trans        where  trans.P_TRANS_CODE=‘1011402‘               and BIZ_ID=‘013‘    group by trans.TERM_ID        ) trans_pay_ydon     trans_basic.BASIC_TERM_ID=trans_pay_yd.TERM_IDleft join( -- 查询电信缴费    select trans.TERM_ID  TERM_ID,               count(1) PAY_DX_Count,               sum(trans.TRAN_AMT) PAY_DX_Money    from   SELFCUR.BIZ_MAIN_TRANS_HIS trans        where  trans.P_TRANS_CODE=‘1011402‘               and BIZ_ID=‘012‘    group by trans.TERM_ID        ) trans_pay_dxon    trans_basic.BASIC_TERM_ID=trans_pay_dx.TERM_IDleft join( -- 电力缴费    select trans.TERM_ID  TERM_ID,               count(1) PAY_DL_Count,               sum(trans.TRAN_AMT) PAY_DL_Money    from   SELFCUR.BIZ_MAIN_TRANS_HIS trans        where  trans.P_TRANS_CODE=‘1011402‘        --重庆电力014 三峡电力 008               and (BIZ_ID=‘014‘ or BIZ_ID=‘008‘)    group by trans.TERM_ID        ) trans_pay_dlon     trans_basic.BASIC_TERM_ID=trans_pay_dl.TERM_IDleft join( -- 自来水缴费    select trans.TERM_ID  TERM_ID,               count(1) PAY_ZLS_Count,               sum(trans.TRAN_AMT) PAY_ZLS_Money    from   SELFCUR.BIZ_MAIN_TRANS_HIS trans        where  trans.P_TRANS_CODE=‘1011402‘        --水务2测试 004 水费 005               and (BIZ_ID=‘004‘ or BIZ_ID=‘005‘)    group by trans.TERM_ID        ) trans_pay_zlson     trans_basic.BASIC_TERM_ID=trans_pay_zls.TERM_IDleft join( -- 燃气缴费    select trans.TERM_ID  TERM_ID,               count(1) PAY_RQ_Count,               sum(trans.TRAN_AMT) PAY_RQ_Money    from   SELFCUR.BIZ_MAIN_TRANS_HIS trans        where  trans.P_TRANS_CODE=‘1011402‘        --再生资源 003                and BIZ_ID=‘003‘     group by trans.TERM_ID        ) trans_pay_rqon     trans_basic.BASIC_TERM_ID=trans_pay_rq.TERM_IDleft join( -- 现金交易 取款    select trans.TERM_ID  TERM_ID,               count(1) CASH_QK_Count,               sum(trans.TRAN_AMT) CASH_QK_Money    from   SELFCUR.BIZ_MAIN_TRANS_HIS trans        where  trans.P_TRANS_CODE=‘1011101‘    group by trans.TERM_ID        ) trans_cash_qkon     trans_basic.BASIC_TERM_ID=trans_cash_qk.TERM_IDleft join( -- 现金交易 存款    select trans.TERM_ID  TERM_ID,               count(1) CASH_CK_Count,               sum(trans.TRAN_AMT) CASH_CK_Money    from   SELFCUR.BIZ_MAIN_TRANS_HIS trans        where  trans.P_TRANS_CODE=‘1011103‘    group by trans.TERM_ID        ) trans_cash_ckon     trans_basic.BASIC_TERM_ID=trans_cash_ck.TERM_IDleft join( -- 现金交易 查询 --余额查询1011001  查询交易明细1011002 积分查询 1011003    select trans.TERM_ID  TERM_ID,               count(1) CASH_CX_Count    from   SELFCUR.BIZ_MAIN_TRANS_HIS trans        where  trans.P_TRANS_CODE=‘1011001‘     group by trans.TERM_ID        ) trans_cash_cxon     trans_basic.BASIC_TERM_ID=trans_cash_cx.TERM_ID    left join( -- 现金交易 转账    select trans.TERM_ID  TERM_ID,               count(1) CASH_ZH_Count,               sum(trans.TRAN_AMT) CASH_ZH_Money    from   SELFCUR.BIZ_MAIN_TRANS_HIS trans        where  trans.P_TRANS_CODE=‘1011104‘    group by trans.TERM_ID        ) trans_cash_zhon     trans_basic.BASIC_TERM_ID=trans_cash_zh.TERM_IDleft join( -- 补登折 存折    select trans.TERM_ID  TERM_ID,               count(1) BUDENG_CZ_Count    from   SELFCUR.BIZ_MAIN_TRANS_HIS trans        where  trans.P_TRANS_CODE=‘1011502‘    group by trans.TERM_ID        ) trans_budeng_czon     trans_basic.BASIC_TERM_ID=trans_budeng_cz.TERM_ID

var text="";// 交易时间 text += " and  trans.P_REQ_DATE  between  ‘"+ reportContext.getParameterValue("startDate").toString().replaceAll("-","") +"‘ and ‘"+reportContext.getParameterValue("endDate").toString().replaceAll("-","") + "‘";// 分行var branchId=reportContext.getParameterValue("branchId");    if(branchId != null && branchId!="" && branchId!="null"){      text += " and branch.INST_ID= " + branchId ;  }// 支行 var subId=reportContext.getParameterValue("subId");    if(subId != null && subId!="" && subId!="null"){      text += " and subbranch.INST_ID= " + subId ;  }// 自助银行 var selfId=reportContext.getParameterValue("selfId");    if(selfId != null && selfId!="" && selfId!="null"){      text += " and self.INST_ID= " + selfId;  }// 终端号var termId=reportContext.getParameterValue("termId");    if(termId != null && termId!="" && termId!="null"){      text += " and trans.TERM_ID= " + termId ;  }// 柜员号var tellNo=reportContext.getParameterValue("tellNo");    if(tellNo != null && tellNo!="" && tellNo!="null"){      text += " and trans.TELL_NO= " + tellNo ;  }// 设备状态var deviceState=reportContext.getParameterValue("deviceState");    if(deviceState != null && deviceState!="" && deviceState!="null"){      text += " and device.DEVICE_STATE in ( " + deviceState + ")" ;  }// queryText中只有一个 --XXXXYYYY--var oldText=this.queryText.split("--XXXXYYYY--");this.queryText=oldText[0]+text+oldText[1];