oracle-外连接left join的应用

需求

 自助设备交易统计

 

输入项	类型	可为空	备注
机构	选择	Y	采用下拉框的形式
终端号	手输	Y	与柜员号二选一
交易柜员号	手输		与终端号二选一
时间	选择	N	时间区间
状态	多选		设备状态：停用、启用

 

输出（新增各业务的交易总额字段）：

 

 

我自己在报表工具birt中写的sql

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_basic
left 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_lt
on 
    trans_basic.BASIC_TERM_ID=trans_pay_lt.TERM_ID
left 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_yd
on 
    trans_basic.BASIC_TERM_ID=trans_pay_yd.TERM_ID
left 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_dx
on
    trans_basic.BASIC_TERM_ID=trans_pay_dx.TERM_ID
left 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_dl
on 
    trans_basic.BASIC_TERM_ID=trans_pay_dl.TERM_ID
left 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_zls
on 
    trans_basic.BASIC_TERM_ID=trans_pay_zls.TERM_ID
left 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_rq
on 
    trans_basic.BASIC_TERM_ID=trans_pay_rq.TERM_ID
left 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_qk
on 
    trans_basic.BASIC_TERM_ID=trans_cash_qk.TERM_ID
left 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_ck
on 
    trans_basic.BASIC_TERM_ID=trans_cash_ck.TERM_ID
left 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_cx
on 
    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_zh
on 
    trans_basic.BASIC_TERM_ID=trans_cash_zh.TERM_ID
left 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_cz
on 
    trans_basic.BASIC_TERM_ID=trans_budeng_cz.TERM_ID

以后用到left join 可以参考上面写的

此外，贴出在birt 的数据集脚本beforeOpen

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