踩坑JAVA导出数据需求，5.7版本MySQL不支持ROW_NUMBER() 窗口函数后的做法

背景：

人力需求想做个导出人员信息的功能，由于平台是低代码结构的，只需要创建视图，然后把查询语句写入进去就好了，但是人员信息、教育情况、家庭情况分开放了三个表，并且人员与教育、家庭为1：N的关系，但是要一行展示出来导出，每个人填写的条数不一这就要用到动态字段了，但是视图不支持动态字段的写法（不支持ROW_NUMBER() 窗口函数）

解决方案：

数据库：

在数据库创建了一个函数作为返回数据使用及数据处理（模拟ROW_NUMBER() 窗口函数）

CREATE DEFINER=`xxxx`@`%` PROCEDURE `v_ygxx_detail_process`()
BEGIN
	#Routine body goes here...
SELECT 
    	y.F_username AS 登录名,
	y.F_name AS 姓名,
	y.F_company AS 归属公司,
	y.F_yjbm AS 一级部门,
	y.F_ejbm AS 二级部门,
	y.F_sjbm AS 三级部门,
	y.F_gw AS 岗位,
	y.F_status AS 状态,
	y.F_rank AS 专业职级,
	y.F_mgr_rank AS 管理职级,
	y.F_census_register AS 户口,
	y.F_register_type AS 户口性质,
	y.F_bank_account_info AS '工资卡(银行名称、开户行全称)',
	y.F_account_no AS '工资卡 (卡号)',
	y.F_qq AS QQ,
	y.F_other_email AS 个人邮箱,
	y.F_nation AS 民族,
	y.F_polity_status AS 政治面貌,
	y.F_graduate_date AS 毕业时间,
	y.F_current_addr AS 现居住地,
	y.F_english_level AS 英语等级,
	y.F_contribute_month AS 上家单位的社保缴至年月,
	y.F_reward_punish_note AS 员工奖惩备注,
	y.F_is_entry AS 是否已办理入职,
	y.F_entry_date AS 入职时间,
	y.F_hire_category AS 录用类别,
	y.F_staff_type AS 人员类别,
	y.F_relation_transfer_note AS 劳动关系转移备注,
	y.F_manager_note AS 管理层备注,
	y.F_communication_expense_limit AS 通讯费额度,
	y.F_surplus_append_note AS 社保公积金新增备注,
	y.F_project_group AS '分组 /项目',
	y.F_responsibility AS 新员工岗位职责,
	y.F_post_note AS 岗位层级,
	y.F_social_security_note AS 社保公积金备注,
	y.F_place AS 员工入职办理地点,
	y.F_staff_business_manger AS 业务上级,
	y.F_work_site AS 工作地点,
	y.F_salary_note AS 薪资部分,
	y.F_salary_start_year AS '入职年份 -薪资计算年',
	y.F_salary_start_month AS '入职月份 -薪资计算月',
	y.F_social_start_year AS '社保办理年份 -新增（当月缴交）年',
	y.F_social_start_month AS '社保办理月份 -新增（当月缴交）月',
	y.F_social_site AS 社保缴纳地,
	y.F_performance_note AS 年终绩效备注,
	y.F_special_note AS 特殊备注,
	y.F_work_history AS 外部工作经历,
	y.F_workmonths AS '工龄 (月)',
	y.F_entrymonths AS '司龄 (月)',
	y.F_has_trialdate AS 是否有试用期,
	y.F_probation_period_limit AS '试用期限（月）',
	y.F_probation_salary AS 试用期工资,
	y.F_official_salary AS 转正工资,
	y.F_expect_official_on AS 预期转正时间,
	y.F_procedure_end_date AS 手续完成转正时间,
	y.F_probation_period_note AS 试用期备注,
	y.F_official_start_year AS '转正年份 -薪资计算年',
	y.F_official_start_month AS '转正月份 -薪资计算月',
	y.F_manager_advice AS 转正类型,
	y.F_bring_forward_month AS '提前转正备注（月）',
	y.F_inner_work_history AS 体系内任职记录,
	y.F_post_handover AS OA审批权限移交人,
	y.F_post_adjust_scope AS 员工岗位调整类别,
	y.F_post_adjust_type AS 员工岗位调整类型,
	y.F_post_adjust_start_year AS '岗位调整年份 -薪资计算年',
	y.F_post_adjust_start_month AS '岗位调整月份 -薪资计算月',
	y.F_renewal_number AS 合同续签次数,
	y.F_contract_start_on AS 合同开始时间,
	y.F_contract_end_on AS 合同到期时间,
	y.F_contract_category AS 劳动合同版本,
	y.F_labour_contract_type AS 合同类型,
	y.F_labour_contract_limit_type AS 合同期限类别,
	y.F_labour_contract_company AS 合同签订公司全称,
	y.F_labour_contract_months AS '合同期限（月）',
	y.F_labour_contract_note AS '劳动合同 /协议备注',
	y.F_inner_contract_work AS 原合同期间从事的主要工作,
	y.F_intern_start_on AS 实习协议起始日期,
	y.F_intern_end_on AS 实习协议结束日期,
	y.F_intern_salary_type AS 实习薪资类型,
	y.F_intern_note AS 实习补贴明细,
	y.F_dimission_reason AS 离职原因,
	y.F_dimission_date AS 离职时间,
	y.F_attendance_end_on AS '考勤、福利补贴 /计薪截止日期',
	y.F_secarity_end_year AS 社保截止年份,
	y.F_secarity_end_month AS 社保截止月份,
	y.F_salary_end_year AS '离职年份 -薪资计算',
	y.F_salary_end_month AS '离职月份 -薪资计算',
	y.F_social_end_year AS '社保办理年份 -减少（当月不缴）',
	y.F_social_end_month AS '社保办理月份 -减少（当月不缴）',
	y.F_social_security_remove_note AS 社保公积金减少备注,
	y.F_dimission_note AS 离职备注,
	y.F_to_blacklist AS 是否加入黑名单,
	y.F_quit_type AS 离职类别,
	y.F_perm_level AS 权限级别,
	y.F_birthday AS 生日,
	y.F_birth_year AS 出生年份,
	y.F_birth_month AS 出生月份,
	y.F_age AS 年龄,
	y.F_constellation AS 报销标准,
	y.F_zjsj AS 直接上级,
	y.F_yglrbz AS 员工录入备注,
	y.F_yglryrzbz AS 员工录入与入职备注,
	y.F_zgwszbm AS 主岗位所在部门,
	y.F_zgwszbmid AS 主岗位所在部门id,
	y.F_sqrid AS 申请人id,
	y.F_gwcode AS '岗位CODE',
	y.F_zjsjzh AS 直接上级账号,
	y.F_ygkzh AS 员工卡账号 ,
    -- 家庭成员表 (w_ygjtqk) 字段拼接
    MAX(CASE WHEN t.rn = 1 THEN t.F_xm END) AS 家庭成员姓名1,
    MAX(CASE WHEN t.rn = 1 THEN t.F_gx END) AS 家庭成员关系1,
    MAX(CASE WHEN t.rn = 1 THEN t.F_gzdw END) AS 家庭成员工作单位1,
    MAX(CASE WHEN t.rn = 1 THEN t.F_lxfs END) AS 家庭成员联系方式1,
    
    MAX(CASE WHEN t.rn = 2 THEN t.F_xm END) AS 家庭成员姓名2,
    MAX(CASE WHEN t.rn = 2 THEN t.F_gx END) AS 家庭成员关系2,
    MAX(CASE WHEN t.rn = 2 THEN t.F_gzdw END) AS 家庭成员工作单位2,
    MAX(CASE WHEN t.rn = 2 THEN t.F_lxfs END) AS 家庭成员联系方式2,
    
    MAX(CASE WHEN t.rn = 3 THEN t.F_xm END) AS 家庭成员姓名3,
    MAX(CASE WHEN t.rn = 3 THEN t.F_gx END) AS 家庭成员关系3,
    MAX(CASE WHEN t.rn = 3 THEN t.F_gzdw END) AS 家庭成员工作单位3,
    MAX(CASE WHEN t.rn = 3 THEN t.F_lxfs END) AS 家庭成员联系方式3,
    
    MAX(CASE WHEN t.rn = 4 THEN t.F_xm END) AS 家庭成员姓名4,
    MAX(CASE WHEN t.rn = 4 THEN t.F_gx END) AS 家庭成员关系4,
    MAX(CASE WHEN t.rn = 4 THEN t.F_gzdw END) AS 家庭成员工作单位4,
    MAX(CASE WHEN t.rn = 4 THEN t.F_lxfs END) AS 家庭成员联系方式4,
    
    MAX(CASE WHEN t.rn = 5 THEN t.F_xm END) AS 家庭成员姓名5,
    MAX(CASE WHEN t.rn = 5 THEN t.F_gx END) AS 家庭成员关系5,
    MAX(CASE WHEN t.rn = 5 THEN t.F_gzdw END) AS 家庭成员工作单位5,
    MAX(CASE WHEN t.rn = 5 THEN t.F_lxfs END) AS 家庭成员联系方式5,
    
    MAX(CASE WHEN t.rn = 6 THEN t.F_xm END) AS 家庭成员姓名6,
    MAX(CASE WHEN t.rn = 6 THEN t.F_gx END) AS 家庭成员关系6,
    MAX(CASE WHEN t.rn = 6 THEN t.F_gzdw END) AS 家庭成员工作单位6,
    MAX(CASE WHEN t.rn = 6 THEN t.F_lxfs END) AS 家庭成员联系方式6,

    -- 教育经历表 (w_ygjyqk) 字段拼接
    MAX(CASE WHEN j.rn = 1 THEN j.F_xlcc END) AS 教育情况学历层次1,
    MAX(CASE WHEN j.rn = 1 THEN j.F_bysj END) AS 教育情况毕业时间1,
    MAX(CASE WHEN j.rn = 1 THEN j.F_byyx END) AS 教育情况毕业院校1,
    MAX(CASE WHEN j.rn = 1 THEN j.F_zy END) AS 教育情况专业1,
    MAX(CASE WHEN j.rn = 1 THEN j.F_xlxz END) AS 教育情况学历性质1,
    
    MAX(CASE WHEN j.rn = 2 THEN j.F_xlcc END) AS 教育情况学历层次2,
    MAX(CASE WHEN j.rn = 2 THEN j.F_bysj END) AS 教育情况毕业时间2,
    MAX(CASE WHEN j.rn = 2 THEN j.F_byyx END) AS 教育情况毕业院校2,
    MAX(CASE WHEN j.rn = 2 THEN j.F_zy END) AS 教育情况专业2,
    MAX(CASE WHEN j.rn = 2 THEN j.F_xlxz END) AS 教育情况学历性质2,
    
    MAX(CASE WHEN j.rn = 3 THEN j.F_xlcc END) AS 教育情况学历层次3,
    MAX(CASE WHEN j.rn = 3 THEN j.F_bysj END) AS 教育情况毕业时间3,
    MAX(CASE WHEN j.rn = 3 THEN j.F_byyx END) AS 教育情况毕业院校3,
    MAX(CASE WHEN j.rn = 3 THEN j.F_zy END) AS 教育情况专业3,
    MAX(CASE WHEN j.rn = 3 THEN j.F_xlxz END) AS 教育情况学历性质3,
    
    MAX(CASE WHEN j.rn = 4 THEN j.F_xlcc END) AS 教育情况学历层次4,
    MAX(CASE WHEN j.rn = 4 THEN j.F_bysj END) AS 教育情况毕业时间4,
    MAX(CASE WHEN j.rn = 4 THEN j.F_byyx END) AS 教育情况毕业院校4,
    MAX(CASE WHEN j.rn = 4 THEN j.F_zy END) AS 教育情况专业4,
    MAX(CASE WHEN j.rn = 4 THEN j.F_xlxz END) AS 教育情况学历性质4

FROM 
    w_ygxx y
LEFT JOIN (
    SELECT 
        *,
        @row_number_t := IF(@current_ref_t = REF_ID_, @row_number_t + 1, 1) AS rn,
        @current_ref_t := REF_ID_
    FROM w_ygjtqk, (SELECT @row_number_t := 0, @current_ref_t := NULL) AS vars

) t ON y.ID_ = t.REF_ID_ AND t.rn <= 6 

LEFT JOIN (
    SELECT 
        *,
        @row_number_j := IF(@current_ref_j = REF_ID_, @row_number_j + 1, 1) AS rn,
        @current_ref_j := REF_ID_
    FROM w_ygjyqk, (SELECT @row_number_j := 0, @current_ref_j := NULL) AS vars
) j ON y.ID_ = j.REF_ID_ AND j.rn <= 6  
where y.F_name is not null

GROUP BY 
    y.ID_;
END

应用层

这个服务使用的是mybatisplus作为orm框架
尝试过直接在xml里写sql结果是不支持解析这么复杂的语句

    List<Map<String, Object>> selectAllEmpsAsMapList();

    <select id="selectAllEmpsAsMapList" statementType="CALLABLE" resultType="java.util.Map">
        {CALL v_ygxx_detail_process()}
    </select>

public void ygxxDetailExport(HttpServletResponse response) throws IOException {
        List<Map<String, Object>> maps = wYgxxService.exportYgxxDetail();
        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("员工信息");
        Row headerRow = sheet.createRow(0);
        String[] headers = {"登录名", "姓名", "归属公司", "劳动合同 /协议备注", "原合同期间从事的主要工作", "实习协议起始日期", "实习协议结束日期", "实习薪资类型", "实习补贴明细", "离职原因", "离职时间", "考勤、福利补贴 /计薪截止日期", "社保截止年份", "社保截止月份", "离职年份 -薪资计算", "离职月份 -薪资计算", "社保办理年份 -减少（当月不缴）", "社保办理月份 -减少（当月不缴）", "社保公积金减少备注", "离职备注", "是否加入黑名单", "离职类别", "权限级别", "生日", "出生年份", "出生月份", "年龄", "报销标准", "钉钉平台 ", "平台号", "是否考勤提醒", "是否启用", "隐私信息查询密码", "直接上级", "员工录入备注", "员工录入与入职备注", "主岗位所在部门", "主岗位所在部门", "申请人", "岗位", "直接上级账号", "员工卡账号 ", "家庭成员姓名1", "家庭成员关系1", "家庭成员工作单位1", "家庭成员联系方式1", "家庭成员姓名2", "家庭成员关系2", "家庭成员工作单位2", "家庭成员联系方式2", "家庭成员姓名3", "家庭成员关系3", "家庭成员工作单位3", "家庭成员联系方式3", "家庭成员姓名4", "家庭成员关系4", "家庭成员工作单位4", "家庭成员联系方式4", "家庭成员姓名5", "家庭成员关系5", "家庭成员工作单位5", "家庭成员联系方式5", "家庭成员姓名6", "家庭成员关系6", "家庭成员工作单位6", "家庭成员联系方式6", "教育情况学历层次1", "教育情况毕业时间1", "教育情况毕业院校1", "教育情况专业1", "教育情况学历性质1", "教育情况学历层次2", "教育情况毕业时间2", "教育情况毕业院校2", "教育情况专业2", "教育情况学历性质2", "教育情况学历层次3", "教育情况毕业时间3", "教育情况毕业院校3", "教育情况专业3", "教育情况学历性质3", "教育情况学历层次4", "教育情况毕业时间4", "教育情况毕业院校4", "教育情况专业4", "教育情况学历性质4"};
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            sheet.autoSizeColumn(i);
        }
        int rowNum = 1;
        for (Map<String, Object> map : maps) {
            Row dataRow = sheet.createRow(rowNum++);
            for (int i = 0; i < headers.length; i++) {
                if (map.get(headers[i]) instanceof BigDecimal){
                    dataRow.createCell(i).setCellValue(((BigDecimal) map.get(headers[i])).toPlainString());
                }else if (map.get(headers[i]) instanceof Date){
                    dataRow.createCell(i).setCellValue((Date) map.get(headers[i]));
                }else {
                    dataRow.createCell(i).setCellValue((String) map.get(headers[i]));
                }
            }
        }
        String fileName = "员工信息" + System.currentTimeMillis();

        try {
            ExcelUtil.downloadExcel(workbook,fileName,response);
        } catch (IOException e) {
            e.printStackTrace();
            log.error("导出员工信息详情失败：", e);
            throw new BaseException("下载失败，请联系xxx!");
        }
    }

感觉用fastExcel更简单但是这个老系统不想引入了，用原有的poi算了
感谢观看，如果有更好的方案，欢迎不吝赐教，交流学习 ^ v ^