mybatis报表，动态列与查询参数+行列转换

 

 

这是报表原型，在这张报表中，使用了动态的列与动态查询参数，动态列与动态查询参数全部使用map将参数传入

map参数：

//拼接查询时间
        for (String month : monthList) {
            List<LocalDate> dateList = new ArrayList<>();
            String year1 = yearList.get(1);
            String day1 = dayList.get(0);
            String day2 = dayList.get(1);
            LocalDate selectDateBegin = this.parseLocalDate(year1, month, day1);
            LocalDate selectDateEnd = this.parseLocalDate(year1, month, day2);
            dateList.add(selectDateBegin);
            dateList.add(selectDateEnd);
            dateMap.put(month, dateList);
            String orderNumberSelect = "orderNumber" + month;
            String averageOrderAmountSelect = "averageOrderAmount" + month;
            String orderAmountSelect = "orderAmount" + month;
            List<String> stringList = new ArrayList<>();
            stringList.add(orderNumberSelect);
            stringList.add(averageOrderAmountSelect);
            stringList.add(orderAmountSelect);
            columnMap.put(month, year1 + "-" + month);
        }
        //去年最后一月
        List<LocalDate> dateListLastYear = new ArrayList<>();
        LocalDate selectDateBegin = this.parseLocalDate(yearList.get(0), monthList.get(monthList.size() - 1), dayList.get(0));
        LocalDate selectDateEnd = this.parseLocalDate(yearList.get(0), monthList.get(monthList.size() - 1), dayList.get(1));
        dateListLastYear.add(selectDateBegin);
        dateListLastYear.add(selectDateEnd);
        dateMap.put(Constants.LAST_YEAR_SAME_MONTH, dateListLastYear);
        columnMap.put(Constants.LAST_YEAR_SAME_MONTH, yearList.get(0) + "-" + monthList.get(monthList.size() - 1));

 

 拼接出两个map，columnMap（"09"，"2018  + 09"），dateMap（"09",List("2018-09-01","2018-09-31")）

本来的查询：

 

使用这两个map作为动态参数传入，在mybatis中进行遍历，并且进行mysql的行列装换：

<select id="getCompany" parameterType="com.jn.ssr.superrescuereporting.web.entity.dto.CustomerMonthSearchDTO"
            resultType="com.jn.ssr.superrescuereporting.web.entity.CustomerMonthEntity" statementType="STATEMENT">
        select
        <if test="param.findStatus == 1">
            companyId,companyName,
        </if>
        <if test="param.findStatus == 0">
            parentCompanyId as companyId,parentCompanyName as companyName,
        </if>
        <foreach collection="param.columnMap" index="month" item="item" separator=" ">
                Max(case countDate when '${item}' then orderNumber else 0 end ) orderNumber${month},
                Max(case countDate when '${item}' then orderAmount else 0 end ) orderAmount${month},
                Max(case countDate when '${item}' then averageOrderAmount else 0 end )averageOrderAmount${month},
        </foreach>
        <if test="param.findStatus == 1">
               parentCompanyId,parentCompanyName,
        </if>serviceType from (
        select serviceType,companyId,parentCompanyId,parentCompanyName,companyName,orderNumber,orderAmount,averageOrderAmount,countDate
        from (select '汇总' serviceType,company.id companyId,company.parent_company_id parentCompanyId,
        parentCompany.company_name parentCompanyName,company.company_name companyName,count(1) orderNumber,
        sum(after_discount_amount) orderAmount,TRUNCATE(sum(after_discount_amount) / count(1), 2) averageOrderAmount,
        date_format(t.create_time, '%Y-%m') countDate
        from or_task_count t
        join operate_service_type type on type.type = t.service_type
        join sp_company company on company.id = t.company_id
        join sp_company parentCompany on company.parent_company_id = parentCompany.id
        <where>
            and t.state = 2 and(
            <foreach collection="param.dateMap" index="key" item="dateList" separator="or">
                t.create_time between
                <foreach collection="dateList" item="dateItem" separator=" and " open=" " close=" ">
                    '${dateItem}'
                </foreach>
            </foreach>)
        </where>
        group by company.id, date_format(t.create_time, '%Y-%m'), parentCompanyId, companyName
        ORDER by parentCompanyId, company.id, date_format(t.create_time, '%Y-%m'))t)t
        <if test="param.findStatus == 0">
            group by parentCompanyId
        </if>
        <if test="param.findStatus == 1">
            group by companyId
        </if>
    </select>

查询出来的效果为（行列装换后）：

其中13代表去年同期

转载于:https://www.cnblogs.com/huanghuanghui/p/9997041.html