在大型的系統(tǒng)開發(fā)中，設計的表比較多，有時候會用一個條件實現(xiàn)多表聯(lián)查，這時候用union all會方便很多：
<!-- 獲取撤單列表 -->
? ? <select id="queryList" parameterType="recallDealBean" resultType="hashmap">
? ? select?
? ? fs.APP_SNO_ as "oappSno",
? ? (select SHORT_NAME_ from CMN_FUND where FUND_CODE_ = fs.FUND_CODE_)||'('||fs.FUND_CODE_||')' as "shortName",
? ? fs.BIZ_FLAG_ as "bizFlag",
? ? fs.APP_AMT_ as "appAmt",
? ? fs.BRANCH_CODE_ as "branchCode",
? ? fs.MODIFY_USER_ as "modifyUser",
? ? fs.CHECK_USER_ as "checkUser"
? ? from?
? ? FND_SUBSCRIBE fs
? ? where?
? ? fs.FINANCE_ACC_ = #{financeAcc}
? ? <![CDATA[ and fs.TRADE_DATE_ >= to_char(sysDate,'YYYYMMDD') ]]>
? ? UNION ALL
? ? select?
? ? fp.APP_SNO_ as "oappSno",
? ? (select SHORT_NAME_ from CMN_FUND where FUND_CODE_ = fp.FUND_CODE_)||'('||fp.FUND_CODE_||')' as "shortName",
? ? fp.BIZ_FLAG_ as "bizFlag",
? ? fp.APP_AMT_ as "appAmt",
? ? fp.BRANCH_CODE_ as "branchCode",
? ? fp.MODIFY_USER_ as "modifyUser",
? ? fp.CHECK_USER_ as "checkUser"
? ? from?
? ? FND_PURCHASE fp
? ? where?
? ? fp.FINANCE_ACC_ = #{financeAcc}
? ? <![CDATA[ and fp.TRADE_DATE_ >= to_char(sysDate,'YYYYMMDD') ]]>
? ? UNION ALL
? ? select?
? ? fr.APP_SNO_ as "oappSno",
? ? (select SHORT_NAME_ from CMN_FUND where FUND_CODE_ = fr.FUND_CODE_)||'('||fr.FUND_CODE_||')' as "shortName",
? ? fr.BIZ_FLAG_ as "bizFlag",
? ? fr.APP_VOL_ as "appVol",
? ? fr.BRANCH_CODE_ as "branchCode",
? ? fr.MODIFY_USER_ as "modifyUser",
? ? fr.CHECK_USER_ as "checkUser"
? ? from?
? ? FND_REDEEM fr
? ? where?
? ? fr.FINANCE_ACC_ = #{financeAcc}
? ? <![CDATA[ and fr.TRADE_DATE_ >= to_char(sysDate,'YYYYMMDD') ]]>
? ? UNION ALL
? ? select?
? ? fc.APP_SNO_ as "oappSno",
? ? (select SHORT_NAME_ from CMN_FUND where FUND_CODE_ = fc.FUND_CODE_)||'('||fc.FUND_CODE_||')' as "shortName",
? ? fc.BIZ_FLAG_ as "bizFlag",
? ? fc.APP_VOL_ as "appVol",
? ? fc.BRANCH_CODE_ as "branchCode",
? ? fc.MODIFY_USER_ as "modifyUser",
? ? fc.CHECK_USER_ as "checkUser"
? ? from?
? ? FND_CONVERT fc
? ? where?
? ? fc.FINANCE_ACC_ = #{financeAcc}
? ? ? <![CDATA[ and fc.TRADE_DATE_ >= to_char(sysDate,'YYYYMMDD') ]]>??
? ? </select>
這樣就查出來了我們想要的列表。
