String sql="select borrower.borrower_id ,BORROWER_NAME,BORROWER_EN_NAME,ADDRESS,INDUSTRY_TYPE,ENTITY_TYPE,OPE_STATUS,RESOLUTION_TYPE,LAST_CON_DATE,loan_sum.sum1,loan_sum.sum2,bp_max.bp_owner from ";
sql+="borrower left join(SELECT BORROWER_ID,sum(LOAN.INCE_OPB*CURRENCY_RATE.rate) sum1,sum(LOAN.CURRENT_UPB*CURRENCY_RATE.rate)sum2 FROM LOAN LEFT OUTER JOIN CURRENCY_RATE ON loan.ori_currency = CURRENCY_RATE.currency ";
sql+="group by BORROWER_ID) loan_sum on borrower.borrower_id=loan_sum.borrower_id left join(SELECT borrower_id,bp_owner FROM bp WHERE bp_version IN (SELECT MAX(bp_version) FROM bp bp2 WHERE bp.borrower_id = bp2.borrower_id AND bp2.app_status = '3405')";
sql+=" )bp_max on borrower.borrower_id=bp_max.borrower_id where 1=1";帮忙分析一下这个 谢谢!
sql+="borrower left join(SELECT BORROWER_ID,sum(LOAN.INCE_OPB*CURRENCY_RATE.rate) sum1,sum(LOAN.CURRENT_UPB*CURRENCY_RATE.rate)sum2 FROM LOAN LEFT OUTER JOIN CURRENCY_RATE ON loan.ori_currency = CURRENCY_RATE.currency ";
sql+="group by BORROWER_ID) loan_sum on borrower.borrower_id=loan_sum.borrower_id left join(SELECT borrower_id,bp_owner FROM bp WHERE bp_version IN (SELECT MAX(bp_version) FROM bp bp2 WHERE bp.borrower_id = bp2.borrower_id AND bp2.app_status = '3405')";
sql+=" )bp_max on borrower.borrower_id=bp_max.borrower_id where 1=1";帮忙分析一下这个 谢谢!
select borrower.borrower_id,BORROWER_NAME,BORROWER_EN_NAME,ADDRESS,INDUSTRY_TYPE,ENTITY_TYPE,OPE_STATUS,
RESOLUTION_TYPE,LAST_CON_DATE,loan_sum.sum1,loan_sum.sum2,bp_max.bp_owner
from
borrower
left join
(
SELECT BORROWER_ID,sum(LOAN.INCE_OPB*CURRENCY_RATE.rate) sum1,sum(LOAN.CURRENT_UPB*CURRENCY_RATE.rate)sum2
FROM LOAN LEFT OUTER JOIN CURRENCY_RATE ON loan.ori_currency = CURRENCY_RATE.currency
group by BORROWER_ID
) loan_sum
on borrower.borrower_id=loan_sum.borrower_id
left join
(
SELECT borrower_id,bp_owner FROM bp
WHERE bp_version IN (SELECT MAX(bp_version)
FROM bp,bp2
WHERE bp.borrower_id = bp2.borrower_id
AND bp2.app_status = '3405 '
)
) bp_max on borrower.borrower_id=bp_max.borrower_id
where 1=1 borrower左关联loan_sum 条件为borrower.borrower_id=loan_sum.borrower_id
结果继续左关联bp_max 条件为 borrower.borrower_id=bp_max.borrower_id
SQL看上去很多,其实都很简单的,当然如果你不输入左右关联等等那就没有办法了