mybatis实现多表联查

矫情吗；* 2022-05-22 09:25 220阅读 0赞

在大型的系统开发中，设计的表比较多，有时候会用一个条件实现多表联查，这时候用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>

这样就查出来了我们想要的列表。