一、需求說明

在sql查詢數據后，對數據分組統計。并最后進行總計。

二、需求分析

最終，我想用sql來實現這個統計和查詢的功能。

那么，怎么又查詢，又統計了？

就用到MySQL的union all關鍵字。

然后，通過mybatis把參數組裝成list，傳入，在利用foreach標簽，循環拼接union all部分即可。

三、代碼實現

Java代碼

    public List<Map<String, Object>>  getPriceData(Long  infoId){//根據info id 查詢price listList<Map<String, Object>> priceList = formPriceService.getVIds(infoId);List<Object> vids = new ArrayList<>();for (Map<String, Object> res : priceList) {vids.add(res.get("variety_id"));}Map<String, Object> params = new HashMap<>();params.put("infoId",infoId);params.put("vids",vids);List<Map<String, Object>> priceData = formPriceService.getPriceData(params);return  priceData;}
------------以下為mapper層代碼-------------@Overridepublic List<Map<String, Object>> getPriceData(Map<String, Object> params) {return formPriceMapper.getPriceData(params);}@Overridepublic List<Map<String, Object>> getVIds(Long id) {return formPriceMapper.getVIds(id);}

xml代碼
getVIds方法

<select id="getVIds" parameterType="Long" resultType="Map">SELECTdistinct price.variety_idFROM`rent_form_price` priceLEFT JOIN rent_form_info infoON	price.form_info_id = info.idLEFT JOIN rent_material_standard_temp stempON price.standard_id = stemp.idLEFT JOIN rent_material_variety_temp vtempON price.variety_id = vtemp.idLEFT JOIN rent_store_info storeON info.store_id = store.idWHERE price.form_info_id = #{formInfoId}</select>

getPriceData方法

    <select id="getPriceData" resultType="Map" parameterType="Map"><foreach collection="vids" item="vid" index="index" separator="union all">SELECTprice.variety_id,price.id,price.form_info_id,info.send_date,info.rent_begin_date,vtemp.variety_name,stemp.standard,price.number,price.rental_number,stemp.convert_amount,price.pound,store.store_name,price.note,stemp.rental_unitFROM`rent_form_price` priceLEFT JOIN rent_form_info info ON price.form_info_id = info.idLEFT JOIN rent_material_standard_temp stemp ON price.standard_id = stemp.idLEFT JOIN rent_material_variety_temp vtemp ON price.variety_id = vtemp.idLEFT JOIN rent_store_info store ON info.store_id = store.idWHEREprice.variety_id = #{vid}AND price.form_info_id = #{infoId}UNION ALLSELECT'','','','','',CONCAT(vtemp.variety_name,'小計:'),'',sum(price.number),sum(price.rental_number),'',sum(price.pound),'','',''FROMrent_form_price priceLEFT JOIN rent_material_variety_temp vtemp ON price.variety_id = vtemp.idWHEREprice.variety_id = #{vid}AND price.form_info_id = #{infoId}</foreach>UNION ALLSELECT'','','','','','本單總計:','',sum(price.number),sum(price.rental_number),'',sum(price.pound),'','',''FROMrent_form_price priceLEFT JOIN rent_material_variety_temp vtemp ON price.variety_id = vtemp.idWHEREform_info_id = #{infoId}</select>

四、報表效果

參考：MyBatis之foreach的用法