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
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 < #{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
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>
7、mybatis中常用的方法
