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 > #{pd.startTime}
</if>
<if test="pd.endTime != '' and pd.endTime != null">
AND a.jh_date < #{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 selltotal
FROM ptsell b WHERE 1=1
<if test="pd.startTime != '' and pd.startTime != null">
and b.sell_date > #{pd.startTime}
</if>
<if test="pd.endTime != '' and pd.endTime != null">
AND b.sell_date < #{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 pttotal
FROM pt_purchase c WHERE 1=1
<if test="pd.startTime != '' and pd.startTime != null">
and c.jh_date < #{pd.startTime}
</if>
<if test="pd.endTime != '' and pd.endTime != null">
AND c.jh_date > #{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 selltotal
FROM ptsell d WHERE 1=1 <if test="pd.startTime != '' and pd.startTime != null">
and d.sell_date < #{pd.startTime}
</if>
<if test="pd.endTime != '' and pd.endTime != null">
AND d.sell_date > #{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 > #{startTime}
</if>
<if test="startTime != '' and startTime != null">
and a.saledate < #{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 > #{pd.startTime}
</if>
<if test="pd.startTime != '' and pd.startTime != null">
and a.saledate < #{pd.endTime}
</if>
GROUP BY a.regist_num
</select>
7、mybatis中常用的方法
