mysql as 动态列 mysql动态行列转换

转载

AI领域布道师 2023-09-03 10:26:04

文章标签 mysql as 动态列 java mybatis mysql springboot 文章分类 MySQL 数据库

业务场景：我们在前端展示表格数据一般有两种，一种是业务的统计问题表单，还有一种就是通过问题表单，转换汇总成结果汇总表单，比如一张发货表单，有发货量，产品，时间，现在需要转换成：产品每个月获取的产品数量。也就是时间字段从 ‘行’提到了‘列’，行转列了，那么就涉及到，根据时间的筛选，来动态得到某个产品几个月的发货量，这里的数据处理就需要用存储过程来实现动态的行转列了。

一、表结构数据与需求

首先先看下业务涉及的表格都有哪些，首先有一个产品表，记录产品、项目、编码信息，其次就是一个发货明细表，记录着编码的发货量、发货时间等信息，表结构如下：

产品信息表

PRODUCT	PROJECT	CODE
A产品	A项目	A001
A产品	B项目	B001
B产品	B项目	B002

这里简要概述下业务逻辑，一个产品会有多个项目，项目会有多个产品，而编码是唯一的，这些逻辑不影响我们做关联，仅做解释。

发货明细表

ITEM_CODE	ACTUAL_SHIPMENT_DATE	QUANTITY	..
A001	2022-11-01	100	..
A001	2022-11-05	200	..
B001	2022-10-01	150	..

产品的发货一般都有涉及产品信息，发货地，发货量，发货时间等，我们的需求是求每个月发货量，所以将编码、发货时间和发货量简要展示。

这两个表，最终合并后形式如下：

PRODUCT	PROJECT	CODE	QUANTITY	2022-11	2022-10	..
A产品	A项目	A001	500	100	50	..
A产品	B项目	B001	600	50	200	..
B产品	B项目	B002	700	100	200	..

数据行转列，把时间行数据转换成列头；发货量其实是针对某个编码的发货，SUM字段表示，业务查询的时间范围的某个编码的发货总量，往后是每个动态月份的按月发货量，比如查询A001编码，22年11月-8月的发货量，那么SUM列就是将4个月发货量求和得到结果值，往后分别是2022-11、2022-10、2022-09、2022-08四列月份，值则为每个月的发货量求和（这里是月内的发货求和）。

二、分析处理思路

这里如果只是静态的明确知道年月有多少的话，直接可以使用case when转行即可，样例如下SQL,对于动态行转列问题难点在于不知道有多少列，也就是年月列，前端的样式组件，应该是没办法处理这种动态列头，组件封装好了下次变化年份就匹配不上了，所以最好从后端数据库入手，数据库有个强大的功能，或许就能满足需求了，存储过程，然后在Mybatis的dao层映射文件调用存储过程的方式来实现，这样做的好处是直接得到了我们想要得数据结构，不需要再进行处理，并且存储过程可以封装复杂的sql语句，供外部直接调用，解决了mybatis无法处理复杂sql语句的问题。存储过程的认识，我这边之前也写了一篇，大家可以了解下，当业务需求在转换数据处理很复杂或者需要动态变化列头的时候，不妨考虑下存储过程

固定的表头时间列的SQL处理如下：

SELECT
	product,project,
	ITEM_CODE,sum(total) total,
	SUM( CASE shipmentDate WHEN '2022-05' THEN total ELSE 0 END ) '2022-05' ,
	SUM( CASE shipmentDate WHEN '2022-06' THEN total ELSE 0 END ) '2022-06' ,
	SUM( CASE shipmentDate WHEN '2022-07' THEN total ELSE 0 END ) '2022-07' 
FROM
	(
	SELECT
		t2.product as product,
		t2.project as project,
		ITEM_CODE,
		sum( QUANTITY ) AS total,
		DATE_FORMAT( ACTUAL_SHIPMENT_DATE, "%Y-%m" ) AS shipmentDate 
	FROM
		发货表 t1 INNER JOIN 编码表 t2 on  t1.ITEM_CODE = t2.code 
		where DATE_FORMAT( ACTUAL_SHIPMENT_DATE, "%Y-%m" ) BETWEEN '2022-05' AND '2022-07'
	GROUP BY
		ITEM_CODE,
		DATE_FORMAT( ACTUAL_SHIPMENT_DATE, "%Y-%m" ) 
	ORDER BY
		shipmentDate DESC 
	) temp 
GROUP BY
	ITEM_CODE

三、解决方案

1.创建存储过程，代码如下：

DELIMITER $$  --重定义分隔符，避开， ；这些在sql中常用的符号，但navicat中创建的存储过程不需要再重定义了，这个客户端已经做了分隔符重定义。
//创建存储过程 

CREATE DEFINER=`dbuser`@`%` PROCEDURE `RowToColumnPro`(
    IN productName VARCHAR(100),
    IN projectName VARCHAR(100),
    IN codeName VARCHAR(20),
    IN startTime VARCHAR(20),
    IN endtTime VARCHAR(20)
)
BEGIN

    --1.创建临时表，将编码表与发货表关联，先得到一个按编码，年月分组后的编码发货数据集，性能有一定提升
    DROP TEMPORARY TABLE IF EXISTS tempCode;  
    CREATE TEMPORARY TABLE tempCode(
        PRODUCT VARCHAR(100),
    	PROJECT VARCHAR(100),
    	`CODE` VARCHAR(100),
	    NUM INT,
	    ACTUAL_SHIPMENT_DATE VARCHAR(100) 
    )
    --------------------------

    --2.设置5个用户变量存储查询的产品信息动态入参条件，注意要判断下非空的情况，根据前端用户的实际传参值
    SET @strWhere=' AND 1=1 ';  -- 联表的where条件设置一个用户变量

	IF  productName<>""   THEN 
    --find_in_set函数，productName 参数可以是一个列表变量如('A产品','B产品')，用来精确匹配每个值，字段值以英文”,”分隔，不能用in,in是匹配常量值
	SET @strWhere1=CONCAT(' AND find_in_set(c.product,"',productName ,'")'); 
	SET @strWhere=CONCAT(@strWhere,@strWhere1); -- 拼接条件
	END IF;

	IF projectName<>"" THEN 
	SET @strWhere2=CONCAT(' AND find_in_set(c.project,"',projectName ,'")');
	SET @strWhere=CONCAT(@strWhere,@strWhere2);
	END IF;

	IF codeName<>"" THEN 
	SET @strWhere3=CONCAT(' AND find_in_set(c.`code`,"',codeName,'")');
	SET @strWhere=CONCAT(@strWhere,@strWhere3);
	END IF;

	IF startTime<>""  THEN 
	SET @strWhere4=CONCAT(' AND DATE_FORMAT(s.ACTUAL_SHIPMENT_DATE,"%Y-%m-%d")>="',startTime,'"');
	SET @strWhere=CONCAT(@strWhere,@strWhere4);
	END IF;  

    IF endTime != "" THEN
	SET @strWhere5=CONCAT('AND DATE_FORMAT(s.ACTUAL_SHIPMENT_DATE,"%Y-%m-%d")<="',endTime,'"');
    SET @strWhere=CONCAT(@strWhere,@strWhere5);
	END IF; 
	-- SELECT @strWhere; 这个是打印出用户变量的拼接值
	------------------------

    --3.定义查询sql赋值@strSelect：两表联表+产品信息动态条件：@strWhere+分组排序：发货时间年月、产品、项目、编码， 最后插入到临时表tempCode
	SET @strSelect=CONCAT(' INSERT INTO tempCode(PRODUCT,
	PROJECT,
	`CODE`,
	NUM,
	ACTUAL_SHIPMENT_DATE) SELECT 
	c.product AS PRODUCT,
	c.project AS PROJECT,
	c.`code` AS `CODE`,
	SUM(s.QUANTITY) AS NUM,
	DATE_FORMAT(s.ACTUAL_SHIPMENT_DATE,"%Y-%m") AS ACTUAL_SHIPMENT_DATE
	FROM 发货表 s 
	INNER JOIN  编码表 c ON s.ITEM_CODE=c.`code` 
	WHERE 1=1 ',@strWhere,
	'GROUP BY DATE_FORMAT(s.ACTUAL_SHIPMENT_DATE,"%Y-%m"),c.product,c.project,c.`code` 
	ORDER BY DATE_FORMAT(s.ACTUAL_SHIPMENT_DATE,"%Y-%m"),c.product,c.project,c.`code`');

-- 	SELECT @strSelect;
-------------------
   --4.这里先处理执行释放这个插入语句@strSelect，固定流程，可以减少每次执行SQL的语法分析
	PREPARE stmt FROM @strSelect; -- 处理动态sql语句
	EXECUTE stmt ;   -- 执行sql语句
	DEALLOCATE PREPARE stmt;  -- 释放PREPARE
	-- SELECT * FROM tempItem;

------------------------
    --5.根据临时表的转换好的产品发货数据，将时间内容动态行转列后存储在@EE用户变量当中
    --  \'  斜杆是转义，因外层套了一组单引号了，里面还要用单引号 ，即转义单引号 \' => ' 或者 ''=>'
	SET @EE='';
	SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(ACTUAL_SHIPMENT_DATE= \'',ACTUAL_SHIPMENT_DATE,'\',NUM,NULL)) AS "',ACTUAL_SHIPMENT_DATE,'"')) INTO @EE
	FROM (SELECT * FROM tempCode) a;

    --6.这个做空判断，主要是方便前端，如果查询的编码和时间段内没有数据，则返回"无数据"字段便于前端判空，就不用加载表格组件
	IF @EE IS NULL THEN
	SET @EE='SUM(IF(ACTUAL_SHIPMENT_DATE= "无数据",NUM,NULL)) AS "无数据",
	SUM(IF(ACTUAL_SHIPMENT_DATE= "无数据",NUM,NULL)) AS "无数据",
	SUM(IF(ACTUAL_SHIPMENT_DATE= "无数据",NUM,NULL)) AS "无数据"';
	END IF;
	
    --7.最终查询sql定义变量，查询临时表，将前面第5点得到的动态时间拼接到查询字段中，然后再同样还得按时间和产品、项目、编码分组，分组后的num字段求和发货量赋值给每个年月，
	SET @QQ=CONCAT('SELECT 
	product,
	project,
	`code`,
	SUM(NUM) AS num,',@EE,
	' FROM tempCode 
	GROUP BY DATE_FORMAT(ACTUAL_SHIPMENT_DATE,"%Y-%m"), PRODUCT,PROJECT,`CODE`');
    
    --8.执行@QQ查询语句变量
	PREPARE stmt1 FROM @QQ; -- 处理动态sql语句
	EXECUTE stmt1 ;          -- 执行sql语句
	DEALLOCATE PREPARE stmt1; -- 释放PREPARE

END $$
DELIMITER ;

这过程有些复杂，注释中我拆分成8个步骤分析就很明朗了。

DELIMITER 定义分隔符，头设置表示以这个符号表示介绍，最后在END后还需加上，尾部设置; 还原sql原生的分隔符
DEFINER=`dbuser`@`%` 用户名@主机， %表示localhost ，设置指定用户访问存储过程权限
参数模式包括三种：IN OUT INOUT
IN表示输入参数 OUT表示输出参数 INOUT表示输入参数又表示输出参数
BEGIN ...END : 主体内容
SET 定义系统变量和用户变量，用户变量定义可以使用set和select两种，
用户变量以‘@’开头，任意字符串命名。
CONCAT 该函数是数据库内置函数，该需求中用来拼接入参条件、时间内容行转列、拼接多个sql
TEMPORARY 临时表，能一定程度提升SQL的执行效率

2.Mybatis映射文件调用存储过程

mapper.java如下：

@Repository
Public interface proDao{
    List<Map> getPro(String productName,String String projectName,String codeName,String startTime,String endTime);
}

dao.xml如下：

<select id='getPro' resultType="java.util.Map" statementType="CALLABLE" >
    CALL RowToColumnPro(#{productName mode=IN},#{projectName mode=IN},#{codeName mode=IN},#{startTime mode=IN},#{endTime mode=IN})
</select>

语法：call 存储过程名称（#{参数名称，mode=参数模式}，#{参数名称，mode=参数模式}）
这里如果参数多，也可以定义一个<parameterMap>标签存储多个参数，然后传递在select 标签中，语句中用？传参：{call saveuser(?, ?, ?)} ，这里注意最外层有个中括号{}
注意，在mybatis中参数模式全部要大写，否则编译时会报错
CALLABLE，告诉mybatis将要执行的是存储过程

接着就是给服务层调用这个dao接口了~，重点核心就在存储过程脚本编写与dao层调用存储过程。掌握起来

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。