项目练习：使用mybatis的foreach标签，实现union all的拼接语句

一、需求说明

在sql查询数据后，对数据分组统计。并最后进行总计。

二、需求分析

最终，我想用sql来实现这个统计和查询的功能。

那么，怎么又查询，又统计了？

就用到MySQL的union all关键字。

然后，通过mybatis把参数组装成list，传入，在利用foreach标签，循环拼接union all部分即可。

三、代码实现

Java代码

    public List<Map<String, Object>>  getPriceData(Long  infoId){
        //根据info id 查询price list
        List<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层代码-------------

    @Override
    public List<Map<String, Object>> getPriceData(Map<String, Object> params) {
        return formPriceMapper.getPriceData(params);
    }

    @Override
    public List<Map<String, Object>> getVIds(Long id) {
        return formPriceMapper.getVIds(id);
    }

xml代码
getVIds方法

<select id="getVIds" parameterType="Long" resultType="Map">
        SELECT
            distinct price.variety_id
        FROM
            `rent_form_price` price
                LEFT JOIN rent_form_info info
                          ON	price.form_info_id = info.id
                LEFT JOIN rent_material_standard_temp stemp
                          ON price.standard_id = stemp.id
                LEFT JOIN rent_material_variety_temp vtemp
                          ON price.variety_id = vtemp.id
                LEFT JOIN rent_store_info store
                          ON info.store_id = store.id
        WHERE price.form_info_id = #{formInfoId}
    </select>

getPriceData方法

    <select id="getPriceData" resultType="Map" parameterType="Map">
        <foreach collection="vids" item="vid" index="index" separator="union all">
            SELECT
            price.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_unit
            FROM
            `rent_form_price` price
            LEFT JOIN rent_form_info info ON price.form_info_id = info.id
            LEFT JOIN rent_material_standard_temp stemp ON price.standard_id = stemp.id
            LEFT JOIN rent_material_variety_temp vtemp ON price.variety_id = vtemp.id
            LEFT JOIN rent_store_info store ON info.store_id = store.id
            WHERE
            price.variety_id = #{vid}
            AND price.form_info_id = #{infoId}

            UNION ALL
            SELECT
            '',
            '',
            '',
            '',
            '',
            CONCAT(vtemp.variety_name,'小计:'),
            '',
            sum(price.number),
            sum(price.rental_number),
            '',
            sum(price.pound),
            '',
            '',
            ''
            FROM
            rent_form_price price
            LEFT JOIN rent_material_variety_temp vtemp ON price.variety_id = vtemp.id
            WHERE
            price.variety_id = #{vid}
            AND price.form_info_id = #{infoId}
        </foreach>
            UNION ALL
            SELECT
            '',
            '',
            '',
            '',
            '',
            '本单总计:',
            '',
            sum(price.number),
            sum(price.rental_number),
            '',
            sum(price.pound),
            '',
            '',
            ''
            FROM
            rent_form_price price
            LEFT JOIN rent_material_variety_temp vtemp ON price.variety_id = vtemp.id
            WHERE
            form_info_id = #{infoId}
    </select>

四、报表效果

参考：MyBatis之foreach的用法