springboot+vue 显示动态列的结果集

该项目通过动态表单实现按项目组汇总的报表生成。采购申请时，用户需选择自定义配置的项目组。代码实现包括XML查询、Mapper接口和Vue前端展示。XML查询通过动态列生成按项目组汇总的报表，支持按年份、月份、部门和费用项目进行分组统计。Mapper接口定义查询方法，接受列分组和查询条件参数。Vue前端使用表格组件展示数据，并支持列自定义和单元格操作。整体实现灵活，适用于动态报表生成需求。

需求

以项目组报表（动态表单）为例：

1. 项目组为用户自定义配置，采购申请时必选项目组
2. 生成按项目组汇总的报表
   

代码实现

代码参考

xml

<select id="selectProjectTeamList" parameterType="projectTeamReportQueryVo" resultType="java.util.HashMap">
        SELECT a.apply_year,a.apply_month,a.dept_name,a.expense_item,SUM(a.apply_amount) total_apply_amount,
            <foreach item="item" collection="columnGroup"  separator=","  index="index">
                MAX(CASE a.project_team_name WHEN #{item} THEN a.apply_amount ELSE 0 END) AS #{item}
            </foreach>
        FROM (
                 SELECT
                     YEAR(apply_time) AS apply_year, MONTH(apply_time) AS apply_month,dept_name,expense_item,project_team_name,IFNULL(SUM(apply_amount),0) AS apply_amount
                 FROM biz_purchase
                 WHERE approval_status='4'
<!--                     <if test="projectTeamReportQueryVo.deptId != null and projectTeamReportQueryVo.deptId != ''">-->
<!--                         AND dept_id = #{projectTeamReportQueryVo.deptId}-->
<!--                     </if>-->
<!--                     <if test="projectTeamReportQueryVo.deptName != null and projectTeamReportQueryVo.deptName != ''">-->
<!--                         AND dept_name = #{projectTeamReportQueryVo.deptName}-->
<!--                     </if>-->
                     <if test="projectTeamReportQueryVo.expenseItem != null and projectTeamReportQueryVo.expenseItem != ''">
                         AND expense_item = #{projectTeamReportQueryVo.expenseItem}
                     </if>
                     <if test="projectTeamReportQueryVo.startDate != null and projectTeamReportQueryVo.startDate != ''">
                         AND apply_time &gt;= #{projectTeamReportQueryVo.startDate}
                     </if>
                     <if test="projectTeamReportQueryVo.endDate != null and projectTeamReportQueryVo.endDate != ''">
                         AND apply_time &lt;= #{projectTeamReportQueryVo.endDate}
                     </if>
                    <if test="projectTeamReportQueryVo.deptIds != null and projectTeamReportQueryVo.deptIds.size() > 0">
                        AND dept_id IN
                        <foreach item="item" collection="projectTeamReportQueryVo.deptIds" open="(" separator="," close=")" index="index">
                            #{item}
                        </foreach>
                    </if>
                 GROUP BY YEAR(apply_time),MONTH(apply_time),dept_name,expense_item,project_team_name
             ) a
        GROUP BY a.apply_year,a.apply_month,a.dept_name,a.expense_item
    </select>

mapper

/**
 * 查询项目团队
 * 参考：<a href="https://www.cnblogs.com/Cloong/p/17268281.html">存储动态列的结果集</a>
 *
 * @param columnGroup              列分组
 * @param projectTeamReportQueryVo 项目团队查询条件
 * @return 项目团队集合
 */
@SuppressWarnings("MybatisXMapperMethodInspection")
List<Map<String, Object>> selectProjectTeamList(@Param("columnGroup") Set<String> columnGroup, @Param("projectTeamReportQueryVo") ProjectTeamReportQueryVo projectTeamReportQueryVo);

vue

 <el-table v-loading="loading" :data="projectTeamList" border show-summary :summary-method="getSummaries">
      <!--      <el-table-column label="序号" type="index" width="220"/>-->
      <el-table-column
          v-for="col in tableColumnList"
          :prop="col.prop"
          :label="col.label"
          :key="col.prop"
          min-width="150"
      >
        <template #default="scope">
          {{ columnFormatter(scope.row, scope.column) }}
          <el-button v-if="col.showCellView && (scope.row[col.prop] != 0)" link type="primary" icon="View"
                     @click="handleCellView(scope.row, scope.column)"/>
        </template>
      </el-table-column>
    </el-table>