1, 有case情况。
select trunc(exf_payment_receipt.work_date),exf_payment_receipt.exchange_code,
exf_payment_receipt.bill_no,exf_payment_type.name, exf_payment_receipt.total_money,
case exf_payment_receipt.status when '0' then '正常' else '已撤销' end,
case exf_payment_receipt.flag when '0' then '未对账'
when '1' then '对账相符'
when '2' then '财政多出'
when '3' then '本行多出'
when '4' then '金额不符'
else '状态不符' end
from exf_payment_receipt, exf_payment, exf_payment_type
where exf_payment_receipt.exf_payment_id = exf_payment.id
and exf_payment_type.code = exf_payment.payment_type_code ;
2, 数据更新 从一个表更新一条记录到另一个表中。infodept表比org_info表多出 datekey列。
insert into infodept select '20130731',* from org_info;
3,按照理财产品 渠道 处理。
select L.deptid,L.cpdm,L.clrq,D.deptname,
sum(case when L.jyqd ='柜面渠道' then 1 else 0 end) as cnt1,
sum(case when L.jyqd ='柜面渠道' then L.amount else 0 end) as amt1,
sum(case when L.jyqd ='网银渠道' then 1 else 0 end) as cnt2,
sum(case when L.jyqd ='网银渠道' then L.amount else 0 end) as amt2
from lccpmx L,infodept D
where L.deptid=D.deptid and D.datekey=L.clrq
and (D.branch1='0000' or D.branch2='0000' or D.branch3='0000'or D.branch4='0000') and L.cpdm='1332'
group by L.deptid,L.cpdm,L.clrq,D.deptname
ORDER BY L.deptid ;
4，另一种方式的case
insert into paymentbusinessincome_q(datekey,year,quarter,deptid,zfjsamt,zjamt,yysramt)
select '2013-03-31' as datekey,'2013' as year,'1' as quarter,deptid,
sum(case when subjid in ('602101') and datekey='2013-03-31' then crcbal-decbal else 0 end) as zfjsamt,--支付结算业务收入
sum(case when subjid in ('6021') and datekey='2013-03-31' then crcbal-decbal else 0 end) as zjamt, --中间业务收入
sum(case when subjid in ('6051') and datekey='2013-03-31' then crcbal-decbal else 0 end)+ -- 1 其他业务收入
sum(case when subjid in ('6061') and datekey='2013-03-31' then crcbal-decbal else 0 end)+ ---2 汇兑收益
sum(case when subjid in ('6101') and datekey='2013-03-31' then crcbal-decbal else 0 end)+ -- 3 公允价值变动收益
sum(case when subjid in ('6111') and datekey='2013-03-31' then crcbal-decbal else 0 end)+ -- 4 投资收益
sum(case when subjid in ('6021') and datekey='2013-03-31' then crcbal-decbal else 0 end)- --B 手续费及佣金收入
sum(case when subjid in ('6421') and datekey='2013-03-31' then decbal-crcbal else 0 end)+ --5 =B-A B手续费及佣金净收入 A 手续费及佣金支出
sum(case when subjid in ('6011') and datekey='2013-03-31' then crcbal-decbal else 0 end)-
sum(case when subjid in ('6411') and datekey='2013-03-31' then decbal-crcbal else 0 end) as yysramt --6=D-C 利息净收入 D 利息收入 C 利息支出
from decrsubjtotal where datekey='2013-03-31'
and deptid in (select deptid from infodept where datekey='2013-03-31' and (branch1='0000' or branch2='0000' or branch3='0000' or branch4='0000'))
group by deptid;