oracle-外连接left join的应用
2024-09-21 16:12:41
需求
自助设备交易统计
输入项 |
类型 |
可为空 |
备注 |
机构 |
选择 |
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, ) PAY_LT_Count, sum(trans.TRAN_AMT) PAY_LT_Money from SELFCUR.BIZ_MAIN_TRANS_HIS trans ' ' 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, ) PAY_YD_Count, sum(trans.TRAN_AMT) PAY_YD_Money from SELFCUR.BIZ_MAIN_TRANS_HIS trans ' ' 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, ) PAY_DX_Count, sum(trans.TRAN_AMT) PAY_DX_Money from SELFCUR.BIZ_MAIN_TRANS_HIS trans ' ' 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, ) PAY_DL_Count, sum(trans.TRAN_AMT) PAY_DL_Money from SELFCUR.BIZ_MAIN_TRANS_HIS trans ' --重庆电力014 三峡电力 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, ) PAY_ZLS_Count, sum(trans.TRAN_AMT) PAY_ZLS_Money from SELFCUR.BIZ_MAIN_TRANS_HIS trans ' --水务2测试 004 水费 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, ) PAY_RQ_Count, sum(trans.TRAN_AMT) PAY_RQ_Money from SELFCUR.BIZ_MAIN_TRANS_HIS trans ' --再生资源 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, ) CASH_QK_Count, sum(trans.TRAN_AMT) CASH_QK_Money from SELFCUR.BIZ_MAIN_TRANS_HIS trans ' 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, ) CASH_CK_Count, sum(trans.TRAN_AMT) CASH_CK_Money from SELFCUR.BIZ_MAIN_TRANS_HIS trans ' 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, ) CASH_CX_Count from SELFCUR.BIZ_MAIN_TRANS_HIS trans ' 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, ) CASH_ZH_Count, sum(trans.TRAN_AMT) CASH_ZH_Money from SELFCUR.BIZ_MAIN_TRANS_HIS trans ' 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, ) BUDENG_CZ_Count from SELFCUR.BIZ_MAIN_TRANS_HIS trans ' 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];
最新文章
- Caffe学习笔记2--Ubuntu 14.04 64bit 安装Caffe(GPU版本)
- Android屏幕适配总结
- 【转】使用:after清除浮动
- mysql添加外键错误
- 代码备份:处理 SUN397 的代码,将其分为 80% 训练数据 以及 20% 的测试数据
- Oracl数据库管理方面的资料(查询sga,查看oracle数据库名称sid,查看oracle数据库名称,查看表空间,修改表空间名称)
- uva - 10833 Supermean(二项式系数,对指数)
- 线程通信机制:共享内存 VS 消息传递
- 开心学习系列学习笔记-----nodejs缺点
- 内存(MRC)
- FlexPaper+SWFTool+操作类=在线预览PDF(转)
- nodejs 开发指南 书中小项目 代码
- weblogic修改jdk版本遇到的问题与解决方法
- [LeetCode] Find Bottom Left Tree Value 寻找最左下树结点的值
- Linux大学实验
- CSRFGuard工具介绍
- bzoj千题计划115:bzoj1024: [SCOI2009]生日快乐
- Xshell通过ssh方式连接Linux服务器,通过Xftp进行文件传输
- 【BZOJ1014】火星人(Splay,哈希)
- python2和python3的编码问题