Oracle-SQL-按⽉统计⾃助终端交易量
SQL实现的⽬标：
基本情况现⾦交易情况转账情况转账交易情况(明细）其它业务情况
⽀⾏名⽀⾏
号
所属⽹
点
⽹点号管理员帐户管理员终端编号
取款笔
数
取款⾦额
存款笔
数
存款⾦额
转账笔
数
转账⾦额
卡
卡
笔
数
卡
卡
⾦
额
卡折笔
数
卡折
⾦额
折
卡
笔
数
折卡
⾦额
折
折
笔
数
折折⾦
额
代缴费
笔数
巴南⽀⾏10101302东泉分理
处
402230080416813378骆涌2300332612715195051433007135620000330804104820
巴南⽀⾏10103201跳⽯分理
处
402230080344302189万敏230018743572940628032732014100510000042700010735100
巴南⽀⾏10100401⽊洞分理
处
402230080350688018唐⾃利23001620674512739.42955348622520608000318500430180181574000
规则，每天每种交易最多统计3笔
完成按⽉统计任务
⽤到的知识点总结：
1） case when的使⽤；
case when an_amt) >3then3else an_amt) end count
case unt is null unt end
2）left join的使⽤；  left join 中可以写where 的条件
--外连接取款
left join
(
select
case when unt) is null then0else unt)  end count,
case when ) is null then0else )  end money,
term_id_in
from
(
select
case when an_amt) >3then3else an_amt) end count,
case when an_amt) is null then0else an_amt)  end money,
to_char(to_date(transQK.p_req_date, 'yyyyMMdd'), 'dd') p_req_date,
term_id_in
from
BIZ_OPER_TRANS transQK
where
-- 取款便民取款
(transQK.P_TRANS_CODE='1011101'or transQK.P_TRANS_CODE='1011231')
and    transQK.p_req_date between to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd') and to_char(add_months(last_day(sysdate),-1),'yyyyMMdd') group by
term_id_in,P_TRANS_CODE,to_char(to_date(transQK.p_req_date, 'yyyyMMdd'), 'dd')
) dayQKIn
group _id_in
) dayQK
_id_in=term.TERMINAL_ID
3）时间函数表⽰上个⽉第⼀天、上个⽉最后⼀天
transCX.p_req_date between to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd') and to_char(add_months(last_day(sysdate),-1),'yyyyMMdd')
4）四舍五⼊⽉笔数/天数
round(( case unt is null unt end+
case unt is null unt end+
case unt is null unt end+
case unt is null unt end+
case unt is null unt end )
/(1+to_char(add_months(last_day(sysdate),-1),'yyyyMMdd')-to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd')),2)
5)  最终的SQL
-----------------------------------终极版终结版-------------------------------------------------------
-- 每⽉每种交易最多统计3笔
-- T05 便民终端
select
parent.inst_code ⽀⾏号,
parent.inst_name ⽀⾏名,
inst.inst_code ⽹点号,
inst.inst_name ⽹点名,
term.admin_Acct 管理员账户 ,
term.admin_Name 管理员,
case unt is null unt end取款笔数,
case is null end取款⾦额,
case unt is null unt end存款笔数,
case is null end存款⾦额,
case unt is null unt end转账笔数,
case is null end转账⾦额,
case unt is null unt end代缴笔数,
case is null end代缴⾦额,
case unt is null unt end查询笔数,
-- 汇总
case unt is null unt end+
case unt is null unt end+
case unt is null unt end+
case unt is null unt end+
case unt is null unt end总笔数,
-
- 这⾥⽤了四舍五⼊
round(( case unt is null unt end+
case unt is null unt end+
case unt is null unt end+
case unt is null unt end+
case unt is null unt end )
/(1+to_char(add_months(last_day(sysdate),-1),'yyyyMMdd')-to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd')),2) ⽇均笔数,
--
case is null end+
case is null end+
case is null end+
case is null end总⾦额
--
from
ops_device_info device ,ops_institution inst,ops_institution parent ,OPS_TERMINAL_INFO term
--外连接取款
left join
(
select
case when unt) is null then0else unt)  end count,
case when ) is null then0else )  end money,
term_id_in
from
(
select
case when an_amt) >3then3else an_amt) end count,
case when an_amt) is null then0else an_amt)  end money,
to_char(to_date(transQK.p_req_date, 'yyyyMMdd'), 'dd') p_req_date,
term_id_in
from
BIZ_OPER_TRANS transQK
where
-- 取款便民取款
(transQK.P_TRANS_CODE='1011101'or transQK.P_TRANS_CODE='1011231')
and    transQK.p_req_date between to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd') and to_char(add_months(last_day(sysdate),-1),'yyyyMMdd') group by
term_id_in,P_TRANS_CODE,to_char(to_date(transQK.p_req_date, 'yyyyMMdd'), 'dd')
) dayQKIn
group _id_in
) dayQK
_id_in=term.TERMINAL_ID
--外连接存款
left join
(
select
case when unt) is null then0else unt)  end count,
case when ) is null then0else )  end money,
term_id_in
from
(
select
case when an_amt) >3then3else an_amt) end count,
case when an_amt) is null then0else an_amt)  end money,
to_char(to_date(transCK.p_req_date, 'yyyyMMdd'), 'dd') p_req_date,
term_id_in
from
BIZ_OPER_TRANS transCK
where
(transCK.P_TRANS_CODE='1011103'or transCK.P_TRANS_CODE='1011232')
and    transCK.p_req_date between
to_char(add_months(last_day(sysdate) +1, -2), 'yyyyMMdd') and
to_char(add_months(last_day(sysdate), -1), 'yyyyMMdd')
group by
term_id_in,P_TRANS_CODE,to_char(to_date(transCK.p_req_date, 'yyyyMMdd'), 'dd')
) dayCKIn
group _id_in
) dayCK
_id_in=term.TERMINAL_ID
--外连接转账
left join
(
select
case when unt) is null then0else unt)  end count,
case when ) is null then0else )  end money,
term_id_in
from
(
select
case when an_amt) >3then3else an_amt) end count,
case when an_amt) is null then0else an_amt)  end money,
to_char(to_date(transZZ.p_req_date, 'yyyyMMdd'), 'dd') p_req_date,
term_id_in
from
BIZ_OPER_TRANS transZZ
where
( transZZ.P_TRANS_CODE='1011105'or transZZ.P_TRANS_CODE='1011203'or transZZ.P_TRANS_CODE='1011206'or
transZZ.P_TRANS_CODE='1011233'or transZZ.P_TRANS_CODE='1011107' )
and    transZZ.p_req_date between to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd') and        to_char(add_months(last_day(sysdate),-1),'yyyyMMdd') group by
term_id_in,P_TRANS_CODE,to_char(to_date(transZZ.p_req_date, 'yyyyMMdd'), 'dd')
) dayZZIn
group _id_in
) dayZZ
_id_in=term.TERMINAL_ID
--代缴费
left join
(
select
case when unt) is null then0else unt)  end count,
case when ) is null then0else )  end money,
term_id_in
from
(
select
case when an_amt) >3then3else an_amt) end count,
case when an_amt) is null then0else an_amt)  end money,
to_char(to_date(transDJ.p_req_date, 'yyyyMMdd'), 'dd') p_req_date,
term_id_in
from
BIZ_OPER_TRANS transDJoracle四舍五入
where
( transDJ.P_TRANS_CODE='1011105'or transDJ.P_TRANS_CODE='1011203'or transDJ.P_TRANS_CODE='1011206'or
transDJ.P_TRANS_CODE='1011233'or transDJ.P_TRANS_CODE='1011107' )
and    transDJ.p_req_date between to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd') and to_char(add_months(last_day(sysdate),-1),'yyyyMMdd') group by
term_id_in,P_TRANS_CODE,to_char(to_date(transDJ.p_req_date, 'yyyyMMdd'), 'dd')
) dayDJIn
group _id_in
) dayDJ
_id_in=term.TERMINAL_ID
--查询
left join
(
select
case when unt) is null then0else unt)  end count,
term_id_in
from
(
select
case when an_amt) >3then3else an_amt) end count,
to_char(to_date(transCX.p_req_date, 'yyyyMMdd'), 'dd') p_req_date,
term_id_in
from
BIZ_OPER_TRANS transCX
where
( transCX.P_TRANS_CODE='1011105'or transCX.P_TRANS_CODE='1011203'or transCX.P_TRANS_CODE='1011206'or
transCX.P_TRANS_CODE='1011233'or transCX.P_TRANS_CODE='1011107' )
and transCX.p_req_date between to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd') and to_char(add_months(last_day(sysdate),-1),'yyyyMMdd') group by
term_id_in,P_TRANS_CODE,to_char(to_date(transCX.p_req_date, 'yyyyMMdd'), 'dd')
) dayCXIn
group _id_in
) dayCX
_id_in=term.TERMINAL_ID
--
where1=1
and device.DEVICE_TYPE='T05'
and term.TERMINAL_ID=device.TERMINAL_ID
and term.inst_id=inst.inst_id
and inst.PARENT_INST_ID=parent.inst_id
--
-- and term.admin_name is not null
and inst.inst_level=3
;
这个sql写的不好，太臃肿，可以⽤交易码分组查询（然后⽤decode函数处理）

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。