创建视图

 

CREATE VIEW v_total_asset
    (cid,fin,wallet)
AS 
SELECT c.cid,nvl((
    SELECT SUM(f.total_num*nvl(
                        CASE 
                           WHEN f.prof_mod='1' THEN 1
                           WHEN f.prof_mod='0' THEN f.prod_net
                           ELSE 1
                        END
                    ,1)) FROM t_fin_held f WHERE f.cid = c.cid 
 ),0) AS fin,nvl((
    SELECT SUM(w.total_amt) FROM t_wallet_balance w WHERE w.cid = c.cid AND w.fund_state = '0'
),0) AS wallet
     FROM  t_customer cc
     


CREATE VIEW v_mgr_cust_asset
    (id,mgr_id,cid,cust_name,risk_level,birthday,phone_number,mark_prop,priority,check_status,mc_desc,fin,wallet,total_asset)
AS 
SELECT m.id,m.mgr_id,m.cid,c.cust_name,c.risk_level,c.birthday,c.phone_number,m.mark_prop,nvl(m.priority,0) AS priority,m.check_status,m.mc_desc,a.fin,a.wallet,(a.fin+a.wallet) AS total_asset
 FROM t_mgr_cust m 
    LEFT JOIN v_total_asset a ON a.cid = m.cid
    LEFT JOIN t_customer c ON c.cid = m.cid