mysql存储过程 动态列名

需求：

筛选某时间段内的面试人员，按部门及岗位进行人数汇总。

技术实现：

mybatis+mysql存储过程

存储过程实现：

DELIMITER &&
DROP PROCEDURE IF EXISTS proc_getworkorder_summary;
CREATE PROCEDURE proc_getworkorder_summary(
	IN beginDate date,
	IN endDate date,
	OUT totalNum varchar(300)
	)
READS SQL DATA
BEGIN

#此处建议在mysql配置文件中进行永久配置
SET GLOBAL group_concat_max_len = 102400;
SET SESSION group_concat_max_len = 102400;
SET @sql = NULL;
SET @num = 0;

DROP TEMPORARY TABLE IF EXISTS tmp_work_order;
IF beginDate IS NOT NULL AND endDate IS NOT NULL THEN
	CREATE TEMPORARY TABLE tmp_work_order
	SELECT * FROM sys_work_order WHERE interview_date BETWEEN beginDate AND endDate;
ELSE
	CREATE TEMPORARY TABLE tmp_work_order
	SELECT * FROM sys_work_order;
END IF;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(if(dept = ''',
      dept,
      ''', num, 0)) as ''',
      dept, ''''
    )
  ) INTO @sql
FROM sys_work_order
WHERE ifnull(dept,'')!=''
ORDER BY dept;

SET @sql = CONCAT(
'select ifnull(job_name,''总计'') as 面试企业,',@sql,
',sum(if(dept =''总计'',num,0)) AS 总计
from (
	select job_name,ifnull(dept,''总计'') as dept,count(id) as num
	from tmp_work_order
	where ifnull(dept,'''')!=''''
	group by job_name,dept
	with rollup
	having job_name is not null
) tmp
group by job_name
with rollup');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT COUNT(id) INTO @num
FROM tmp_work_order;
SET totalNum=CONCAT('总条目',@num,'条');

SELECT COUNT(id) INTO @num
FROM tmp_work_order WHERE ifnull(dept,'')!='' and dept not like '%中山%';
SET totalNum=CONCAT(totalNum,'，四川',@num,'条');

SELECT COUNT(id) INTO @num
FROM tmp_work_order WHERE dept like '%中山%';
SET totalNum=CONCAT(totalNum,'，中山',@num,'条');

SELECT COUNT(id) INTO @num
FROM tmp_work_order WHERE ifnull(dept,'')='';
SET totalNum=CONCAT(totalNum,'，部门为空',@num,'条');

SELECT ifnull(SUM(num),0) INTO @num
FROM (
	SELECT COUNT(id) as num
	FROM tmp_work_order
	GROUP BY mobile,interview_date
	HAVING COUNT(id)>1
)t;
SET totalNum=CONCAT(totalNum,'。其中重复数据',@num,'条');

END &&

DELIMITER ;

调用该存储过程：

call proc_getworkorder_summary('2020-07-11','2020-07-11',@totalNum);
select @totalNum;

得到如下结果：

mybatis实现：

<select id="getSummary" statementType="CALLABLE" resultType="java.util.LinkedHashMap">
        {call proc_getworkorder_summary(
              #{beginDate,jdbcType=DATE,mode=IN},
              #{endDate,jdbcType=DATE,mode=IN},
              #{totalNum,jdbcType=VARCHAR,mode=OUT}
        )}
</select>

Dao层：

List<Map<String, Object>> getSummary(Map<String, Object> params);

Service层：

//获取汇总结果集
List<Map<String, Object>> list = this.baseMapper.getSummary(params);
//获取totalNum
String totalNum=params.get("totalNum");