我只是讨论效率,不是说刚才的语句啦我考个源码给看看 tp.append("( select polno,plan_code,mgr_fee/(case total_fee when 0 then 1 else total_fee end) as mgr_fee from( \n"); tp.append(" select polno,plan_code,sum((case tag when 1 then fee else 0 end)) as mgr_fee,sum((case tag when 2 then fee else 0 end)) as total_fee from \n"); tp.append(" ( \n"); tp.append(" select b.polno,a.plan_code,sum((case a.amt_type when 42 then -1*a.tran_amt else a.tran_amt end)*b.Buy_Price_Diff) \n"); tp.append(" as fee, 1 as tag \n"); tp.append(" from t_pol_capital a,t_pol_main b \n"); tp.append(" where a.polno=b.polno \n"); tp.append(" and a.amt_type in (39,40,41,42,43) \n"); tp.append(" and a.gained_date is not null \n"); tp.append(" and a.plan_code='2302' \n"); tp.append(" and a.fcd >=to_date( '" + beginDate + "' ,'yyyy-mm-dd') \n"); tp.append(" and a.fcd <to_date( '" + endDate + "' ,'yyyy-mm-dd')+1 \n"); tp.append(" group by b.polno,a.plan_code \n"); tp.append(" union all \n"); tp.append(" select f.polno,f.plan_code,sum((case f.amt_type when 42 then -f.tran_amt else f.tran_amt end)) as fee, 2 as tag \n"); tp.append(" from t_pol_capital f \n"); tp.append(" where f.amt_type in (39,40,41,42,43) \n"); tp.append(feeType_tp + " \n"); tp.append(" and f.fcd >=to_date( '" + beginDate + "' ,'yyyy-mm-dd') \n"); tp.append(" and f.fcd <to_date( '" + endDate + "' ,'yyyy-mm-dd')+1 \n"); tp.append(" and f.plan_code='2302' \n"); tp.append(" group by f.polno,f.plan_code \n"); tp.append(" ) \n"); tp.append(" group by polno,plan_code \n"); tp.append(" ) \n"); tp.append(" ) b \n"); tp.append("where a.policy_code = b.polno(+) \n"); tp.append(" and a.internal_id = b.plan_code(+) \n");
tp.append(" select polno,plan_code,sum((case tag when 1 then fee else 0 end)) as mgr_fee,sum((case tag when 2 then fee else 0 end)) as total_fee from \n");
tp.append(" ( \n");
tp.append(" select b.polno,a.plan_code,sum((case a.amt_type when 42 then -1*a.tran_amt else a.tran_amt end)*b.Buy_Price_Diff) \n");
tp.append(" as fee, 1 as tag \n");
tp.append(" from t_pol_capital a,t_pol_main b \n");
tp.append(" where a.polno=b.polno \n");
tp.append(" and a.amt_type in (39,40,41,42,43) \n");
tp.append(" and a.gained_date is not null \n");
tp.append(" and a.plan_code='2302' \n");
tp.append(" and a.fcd >=to_date( '" + beginDate + "' ,'yyyy-mm-dd') \n");
tp.append(" and a.fcd <to_date( '" + endDate + "' ,'yyyy-mm-dd')+1 \n");
tp.append(" group by b.polno,a.plan_code \n");
tp.append(" union all \n");
tp.append(" select f.polno,f.plan_code,sum((case f.amt_type when 42 then -f.tran_amt else f.tran_amt end)) as fee, 2 as tag \n");
tp.append(" from t_pol_capital f \n");
tp.append(" where f.amt_type in (39,40,41,42,43) \n");
tp.append(feeType_tp + " \n");
tp.append(" and f.fcd >=to_date( '" + beginDate + "' ,'yyyy-mm-dd') \n");
tp.append(" and f.fcd <to_date( '" + endDate + "' ,'yyyy-mm-dd')+1 \n");
tp.append(" and f.plan_code='2302' \n");
tp.append(" group by f.polno,f.plan_code \n");
tp.append(" ) \n");
tp.append(" group by polno,plan_code \n");
tp.append(" ) \n"); tp.append(" ) b \n");
tp.append("where a.policy_code = b.polno(+) \n");
tp.append(" and a.internal_id = b.plan_code(+) \n");