SQL练习—4

select temp.*,
       (select sum(pv)
          from (select invest.id,                      
                       (select sum(trunc(f.debt_proportion * l.amount_spare, 2))
                          from [e-mail]    l,
                               [e-mail] f
                         where l.id = f.loan_id
                           and f.acct_status in (1, 3, 4)
                           and l.acct_status in (1, 3, 4)
                           and f.ledger_id in
                               (select t.id
                                  from [e-mail] t
                                 where t.account in
                                       (select t1.acc_id
                                          from ft_t_invest_info t1
                                         where t1.id = invest.id))) as pv
                  from ft_t_invest_info invest
                 where customer_id = 111)) as 总PV
  from (select invest.id,
               (select t.cr_customer_number
                  from [e-mail] t
                 where t.id = invest.customer_id) || '-' ||
               invest.lending_no as 出借编号,
              
               (select NODE_NAME
                  from FT_T_FLOW_DEF c
                 where invest.flow_id = c.node_code) as 流程ID,
              
               (select data_name
                  from sys_t_dictionary b
                 where invest.product_type = b.data_code) as 理财产品,
              
               (select sum(trunc(f.debt_proportion * l.amount_spare, 2))
                  from [e-mail]    l,
                       [e-mail] f
                 where l.id = f.loan_id
                   and f.acct_status in (1, 3, 4)
                   and l.acct_status in (1, 3, 4)
                   and f.ledger_id in
                       (select t.id
                          from [e-mail] t
                         where t.account in
                               (select t1.acc_id
                                  from ft_t_invest_info t1
                                 where t1.id = invest.id))) as pv,
              
               invest.invest_amt as 投资金额
          from ft_t_invest_info invest
         where customer_id = 111) temp;