【工作精华】 多表连接的 金额加减操作

一定要记住 :如果多表连接后存在金额的 加减操作的话,那么在表连接的时候一定要按连接字段进行分组 加总金额后 再做表连接

即便是left join 也不例外

否则就会出现下面的数据。 可以看到[OTHER_TABLE_CREDIT]列是rs2表中的一个条数据,结果和rs1 关联后,该金额被多增加了5倍，显然这是不正确的，

所以需要把rs1也汇总之后再做表连接

CUST_NO	NORMAL_BAL	OTHER_TABLE_CREDIT	BAIL_RECEIPT_NATIONAL_DEBT	sum_bal
510290000003327	76000000	1284800000	0	1360800000
510290000003327	140000000	1284800000	0	1424800000
510290000003327	150000000	1284800000	0	1434800000
510290000003327	229000000	1284800000	0	1513800000
510290000003327	117200000	1284800000	0	1402000000
510290000003327	90000000	1284800000	0	1374800000

SELECT '2013-05-31',

tab1.cust_no,

tab2.cust_name,

tab1.normal_bal,

tab1.other_table_credit,

tab1.bail_receipt_national_debt,

tab1.num_bal

FROM (SELECT nvl(rs1.cust_num, nvl(rs2.cust_num, rs3.cust_num)) AS cust_no,

rs1.normal_bal AS normal_bal,

rs1.concerned_bal AS concerned_bal,

rs1.subprime_bal AS subprime_bal,

rs1.doubtful_bal AS doubtful_bal,

rs1.loss_bal AS loss_bal,

nvl(rs2.other_table_credit, 0) AS other_table_credit,

nvl(rs3.bail_receipt_national_debt, 0) AS bail_receipt_national_debt,

(nvl(rs1.normal_bal, 0) + nvl(rs1.concerned_bal, 0) +

nvl(rs1.subprime_bal, 0) + nvl(rs1.doubtful_bal, 0) +

nvl(rs1.loss_bal, 0) + nvl(rs2.other_table_credit, 0) -

nvl(rs3.bail_receipt_national_debt, 0)) AS num_bal

FROM (---------rs1:拆放同业余额明细数

---------rs1:再次算出每个客户下面五级分类的余额各有多少

SELECT t.cust_num,

DECODE(t.cbrc_grade,'N',fnc_cmb_ccy_conversion(t.begin_date,t.currency,nvl(t.balance,0),'CNY'),0) AS normal_bal   ,

DECODE(t.cbrc_grade,'C',fnc_cmb_ccy_conversion(t.begin_date,t.currency,nvl(t.balance,0),'CNY'),0) AS concerned_bal,

DECODE(t.cbrc_grade,'S',fnc_cmb_ccy_conversion(t.begin_date,t.currency,nvl(t.balance,0),'CNY'),0) AS subprime_bal ,

DECODE(t.cbrc_grade,'D',fnc_cmb_ccy_conversion(t.begin_date,t.currency,nvl(t.balance,0),'CNY'),0) AS doubtful_bal ,

DECODE(t.cbrc_grade,'L',fnc_cmb_ccy_conversion(t.begin_date,t.currency,nvl(t.balance,0),'CNY'),0) AS loss_bal

FROM cmb_dm_markeyinfotab t

WHERE t.on_off_flag = '0'

AND t.trade_type in ('2', '10')

AND t.begin_date = '2013-05-31'

AND t.cust_num = '510290000003327'

) rs1

FULL JOIN

---------rs2:其他表内授信明细数

(SELECT t2.cust_num, SUM(nvl(t2.bal, 0)) AS other_table_credit

FROM (SELECT t.cust_num,

fnc_cmb_ccy_conversion(t.begin_date,t.currency,t.balance,'CNY') AS bal

FROM cmb_dm_markeyinfotab t

WHERE t.on_off_flag = '0'

AND t.trade_type = '1'

AND t.begin_date = '2013-05-31'

UNION ALL

SELECT t1.cust_num,

fnc_cmb_ccy_conversion(t1.begin_date,t1.currency,t1.balance,'CNY') AS bal

FROM cmb_dm_assetsaletab t1

WHERE t1.busi_type = '1'

AND t1.fina_code is not null --20130620 add.

AND t1.begin_date = '2013-05-31') t2 where t2.cust_num = '510290000003327'

GROUP BY t2.cust_num) rs2

ON rs1.cust_num = rs2.cust_num

---------rs3:保证金、银行存单、国债明细数

LEFT JOIN (SELECT t3.cust_no as cust_num,

SUM(nvl(t3.coll_mk_val, 0)) as bail_receipt_national_debt

FROM cmb_dm_collainfotab t3

WHERE t3.coll_typ in ('30', '41')

AND t3.begin_date <= '2013-05-31'

AND t3.end_date > '2013-05-31'

GROUP BY t3.cust_no) rs3

ON rs1.cust_num = rs3.cust_num) tab1

-------------tab2:将tab1的明细关联上对公客户信息表取出客户名称、集团号、集团名称

LEFT JOIN (SELECT t4.cust_num,

t4.cust_name,

NVL(t4.cust_grp_no,t4.cust_num) AS cust_grp_no,

NVL(t4.cust_grp_nam,t4.cust_name) AS cust_grp_nam

FROM cmb_dm_ccustomertab t4

WHERE t4.begin_date <= '2013-05-31'

AND t4.end_date > '2013-05-31') tab2

ON tab1.cust_no = tab2.cust_num;