mysql全连接的使用

1、<select id="getlistPageTotalByTime" resultType="pd"> SELECT * FROM (SELECT a.ptregist_num AS num, a.pt_name AS pt_name, SUM(a.number)-SUM(a.recount) AS pttotal FROM pt_purchase a WHERE 1=1 <if test="pd.startTime != '' and pd.startTime != null"> and a.jh_date &gt; #{pd.startTime} </if> <if test="pd.endTime != '' and pd.endTime != null"> AND a.jh_date &lt; #{pd.endTime} </if> GROUP BY a.ptregist_num ) AS pt

2、 LEFT JOIN(SELECT b.pt_regist number,b.pt_name AS pt_name1,SUM(b.sell_num)-SUM(b.recount) AS selltotalFROM ptsell b WHERE 1=1<if test="pd.startTime != '' and pd.startTime != null">and b.sell_date &gt; #{pd.startTime}</if><if test="pd.endTime != '' and pd.endTime != null">AND b.sell_date &lt; #{pd.endTime}</if> GROUP BY b.pt_regist) AS sell ON pt.num = sell.number

3、UNION SELECT * FROM(SELECT c.ptregist_num num,c.pt_name AS pt_name,SUM(c.number)-SUM(c.recount) AS pttotalFROM pt_purchase c WHERE 1=1<if test="pd.startTime != '' and pd.startTime != null">and c.jh_date &lt; #{pd.startTime}</if><if test="pd.endTime != '' and pd.endTime != null">AND c.jh_date &gt; #{pd.endTime}</if> GROUP BY c.ptregist_num ) AS pt1

4、 RIGHT JOIN(SELECT d.pt_regist number,d.pt_name AS pt_name1,SUM(d.sell_num)-SUM(d.recount) AS selltotalFROM ptsell d WHERE 1=1 <if test="pd.startTime != '' and pd.startTime != null">and d.sell_date &lt; #{pd.startTime}</if><if test="pd.endTime != '' and pd.endTime != null">AND d.sell_date &gt; #{pd.endTime}</if> GROUP BY d.pt_regist) AS sell1 ON pt1.num = sell1.number</select>

5、MySQL不支持全外连接，所以只能采取关键字UNION来联合左、右连接的方法达到目的；<select id="getSeedSellTotalByType" resultType="pd"> SELECT a.vt_name AS name, a.unitcode AS unitcode , SUM(a.number)-SUM(a.recount) AS VALUE FROM seedledger a WHERE 1=1 <if test="startTime != '' and startTime != null"> and a.saledate &gt; #{startTime} </if> <if test="startTime != '' and startTime != null"> and a.saledate &lt; #{endTime} </if> GROUP BY a.unitcode </select>

6、<select id="getlistPageHfSellTotalByType" resultType="pd"> SELECT a.regist_num as num, a.fertName as name, SUM(COUNT)-SUM(recount) as value FROM fertilizerledger a WHERE 1=1 <if test="pd.startTime != '' and pd.startTime != null"> and a.saledate &gt; #{pd.startTime} </if> <if test="pd.startTime != '' and pd.startTime != null"> and a.saledate &lt; #{pd.endTime} </if> GROUP BY a.regist_num </select>

7、mybatis中常用的方法