Mysql查询动态行转动态列，并使用mybatis执行导出

作者: AC编程 | 来源:发表于2021-10-26 13:59 被阅读0次

一、代码

1.1 Controller

  @GetMapping("/export")
    @ApiOperation(value="导出excel")
    public void export(QuerySurveyAnswerVO vo, HttpServletResponse response) {
        Survey survey = surveyService.get(vo.getSurveyId());
        String filename = survey.getSurveyName()+"(答卷汇总)"+".xlsx";
        List<LinkedHashMap<String, String>> datas = surveyAnswerService.listExportDatas(vo);
        ExcelWriter writer = ExcelUtil.getWriter(true);
        //设置列宽
        writer.setColumnWidth(-1, 18);
        writer.setOnlyAlias(true);
        writer.write(datas, true);
        super.exportExcel(response, writer, filename);
    }

1.2 查询参数VO

@Data
@NoArgsConstructor
@AllArgsConstructor
@SuppressWarnings("serial")
@ApiModel(value = "QuerySurveyAnswerVO")
public class QuerySurveyAnswerVO {

    @ApiModelProperty(value = "调查问卷ID")
    private Long surveyId;

    @ApiModelProperty(value = "账号")
    private String markId;

    @ApiModelProperty(value = "昵称")
    private String nickName;

    @ApiModelProperty(value = "手机号码")
    private String mobile;

    @ApiModelProperty(value = "状态(0=全部；1=登录用户；2=游客)")
    private Integer anonymousState;
}

1.3 service

public interface SurveyAnswerService {
     /**
     * 导出数据列表
     * @param vo
     * @return
     */
    List<LinkedHashMap<String, String>> listExportDatas(QuerySurveyAnswerVO vo);
}

@Override
    public List<LinkedHashMap<String, String>> listExportDatas(QuerySurveyAnswerVO vo) {
        //获取动态拼接sql
        String dynamicQuestions = mapper.dynamicQuestions(vo.getSurveyId());

        log.info("dynamicQuestions:"+dynamicQuestions);

        String markIdSql = "";
        String nickNameSql = "";
        String mobileSql = "";
        String anonymousStateSql = "";

        if (StringUtil.isNotEmpty(vo.getMarkId())) {
            markIdSql = " and a.mark_id like '%" + vo.getMarkId() + "%'";
        }

        if (StringUtil.isNotEmpty(vo.getNickName())) {
            nickNameSql = " and a.nick_name like '%" + vo.getNickName() + "%'";
        }

        if (StringUtil.isNotEmpty(vo.getMobile())) {
            mobileSql = " and a.mobile like '%" + vo.getMobile() + "%'";
        }

        if (vo.getAnonymousState() != null) {
            if (vo.getAnonymousState() == 1) {
                anonymousStateSql = " and a.member_id is not null ";
            }

            if (vo.getAnonymousState() == 2) {
                anonymousStateSql = " and a.member_id is null ";
            }
        }

        //手动与固定列拼接
        String sql = "select a.mark_id as 用户ID," +
                "if(a.mark_id='',\"游客\",a.nick_name) as 用户昵称," +
                "a.mobile as 手机号," +
                "case a.sex" +
                " when 'MEN' then '男' " +
                " when 'WOMEN' then '女' " +
                " when 'UNKNOW' then '保密' " +
                " end as 性别," +
                "a.answer_time as 参与时间," +
                dynamicQuestions +
                " from t_survey_answer_item ai JOIN t_survey_answer a on ai.survey_answer_id = a.id " +
                " where ai.deleted=0 and a.deleted=0 and a.survey_id='" + vo.getSurveyId() + "'" +
                markIdSql +
                nickNameSql +
                mobileSql +
                anonymousStateSql +
                " group by a.mark_id,a.nick_name,a.mobile,a.sex,a.answer_time" +
                " order by a.answer_time desc";

        log.info("export sql :"+sql);

        //查询结果
        List<LinkedHashMap<String, String>> mapList = mapper.listExportDatas(sql);
        return mapList;
    }

1.4 mybaits-mapper

public interface SurveyAnswerMapper extends BaseMapper<SurveyAnswer> {

    /**
     * 题目集合（动态）
     * @return
     */
    String dynamicQuestions(@Param("surveyId")Long surveyId);

    /**
     * 导出数据列表
     * @param sql
     * @return
     */
    List<LinkedHashMap<String, String>> listExportDatas(String sql);
}

1.5 mybaits.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.alanchen.mapper.SurveyAnswerMapper">

    
    <select id="dynamicQuestions" resultType="java.lang.String">
        SELECT
         @sql :=GROUP_CONCAT(DISTINCT
          CONCAT(
           'Max(IF(ai.question_name = ''',
           a.question_name,
           ''', ai.item_name, '''')) AS ''',
           replace(a.question_name,'.',''), ''''
          )
           order by order_number asc
         )
        FROM (SELECT DISTINCT question_name,order_number FROM t_survey_question where deleted = 0 and survey_id=#{surveyId}) a,
        (SELECT @sql:="") s
    </select>

    <select id="listExportDatas" parameterType="java.lang.String" resultType="java.util.LinkedHashMap">
       ${sql}
    </select>

</mapper>

三、数据表结构

CREATE TABLE `t_survey` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `survey_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '问卷ID',
  `survey_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '问卷标题',
  `remark` varchar(200) NOT NULL DEFAULT '' COMMENT '问卷描述',
  `publish_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'NOT_ENABLED' COMMENT '状态',
  `active` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否立即启用',
  `anonymous` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否匿名',
  `date_start` datetime DEFAULT NULL COMMENT '开始日期',
  `date_end` datetime DEFAULT NULL COMMENT '结束日期',
  `publisher_id` bigint DEFAULT NULL COMMENT '发布者ID',
  `publisher_name` varchar(50) DEFAULT '' COMMENT '发布者名字',
  `join_number` int DEFAULT '0' COMMENT '参与人数',
  `link_url` varchar(100) DEFAULT NULL COMMENT '访问链接',
  `question_num` int DEFAULT '0' COMMENT '题目数',
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `modify_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=186831853387778 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AVG_ROW_LENGTH=2048 COMMENT='问卷调查'

CREATE TABLE `t_survey_answer` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `survey_id` bigint NOT NULL DEFAULT '0' COMMENT '问卷ID',
  `member_id` bigint DEFAULT NULL COMMENT '会员ID',
  `mobile` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '手机号码',
  `nick_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '昵称',
  `mark_id` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '账号',
  `icon_url` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '用户头像地址',
  `sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户性别 UNKNOW保密 MEN男 WOMEN女',
  `answer_time` datetime DEFAULT NULL COMMENT '答题时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `modify_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=189434090356738 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AVG_ROW_LENGTH=2048 COMMENT='用户答卷'

CREATE TABLE `t_survey_answer_item` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `survey_id` bigint NOT NULL DEFAULT '0' COMMENT '问卷ID',
  `survey_answer_id` bigint NOT NULL DEFAULT '0' COMMENT '用户答卷ID',
  `question_id` bigint NOT NULL DEFAULT '0' COMMENT '题目ID',
  `question_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '题目名称',
  `serial_number` int DEFAULT '0',
  `question_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'SINGLE_CHOICE' COMMENT '题型',
  `question_item_id` bigint DEFAULT NULL COMMENT '选项ID',
  `item_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '选项内容',
  `icon` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '图标',
  `order_number` int DEFAULT '0',
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `modify_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=189434090356743 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AVG_ROW_LENGTH=2048 COMMENT='用户答卷明细'

CREATE TABLE `t_survey_question` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `survey_id` bigint NOT NULL DEFAULT '0' COMMENT '问卷ID',
  `question_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '题目名称',
  `question_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'SINGLE_CHOICE' COMMENT '题型',
  `required` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否必填',
  `least` int DEFAULT NULL COMMENT '最少选择',
  `most` int DEFAULT NULL COMMENT '最多选择',
  `order_number` int DEFAULT '0' COMMENT '排序序号',
  `forward_order_number` int DEFAULT NULL COMMENT '跳题序号（多选题时）',
  `forward_question_id` bigint DEFAULT NULL COMMENT '跳题ID（多选时）',
  `serial_number` int DEFAULT '0',
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `modify_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=186887039942735 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AVG_ROW_LENGTH=2048 COMMENT='问卷调查题目'

CREATE TABLE `t_survey_question_item` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `survey_id` bigint NOT NULL DEFAULT '0' COMMENT '问卷ID',
  `question_id` bigint NOT NULL DEFAULT '0' COMMENT '题目ID',
  `item_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '选项内容',
  `icon` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '图标',
  `item_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'TEXT' COMMENT '类型',
  `order_number` int DEFAULT '0' COMMENT '排序序号',
  `forward_order_number` int DEFAULT NULL COMMENT '跳题序号',
  `forward_question_id` bigint DEFAULT NULL COMMENT '跳题ID',
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `modify_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=186887039942742 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AVG_ROW_LENGTH=2048 COMMENT='题目选项'

四、MySQL GROUP_CONCAT函数长度限制

平常使用可能发现不了问题，在处理大数据的时候，会发现内容被截取了，其实MYSQL内部对这个是有设置的，默认不设置的长度是1024个字符，如果我们需要更大，就需要手工去修改配置。

1、查看当前mysql group_concat_max_len

show variables like 'group_concat_max_len'

2、如果不方便重启mysql 可以在mysql状态通过命令设置，如

SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;

此种方式在mysql重启后会读取配置文件重新设置，会导致设置失效，所以建议依旧要修改配置文件。

3、修改配置文件：my.ini
在[mysqld]下新增配置：group_concat_max_len = 102400
重启，通过方式1查看即可。

4、不限制大小
有时我们并不知需要多大的字节才能满足需求，此种情况可以考虑不设置最大字节（即采用最大字节数）即在配置文件设置group_concat_max_len=-1

五、备注

1、导出用的是Hutool导出工具类

六、参考资料

mysql查询动态行转动态列，并使用mybatis执行

MySQL 动态行转列（列值转换列名）

Mysql查询动态行转动态列，并使用mybatis执行导出

一、代码

1.1 Controller

1.2 查询参数VO

1.3 service

1.4 mybaits-mapper

1.5 mybaits.xml

三、数据表结构

四、MySQL GROUP_CONCAT函数长度限制

五、备注

六、参考资料

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读