目录
1、概述
1.1原表格结构
1.2 需求
1.3 解决方案
2、动态行转列sql语句
2.1sql语句
2.2sql语句解析
3、mysql构建存储过程
4、mybatis调用mysql存储过程
1、概述
1.1原表格结构
base表:
plan表:
1.2 需求
根据时间进行范围查询并且将时间由行转化为列展示,如下图:
1.3 解决方案
对于动态行转列问题难点在于要转为列的行并不是固定的,所以会导致在后端做转化比较复杂繁琐,在前端做转化又会遇到组件已封装完善,拿到后端返回的数据进行行转列的处理不现实,有可能无法使用原有已封好的组件。于是,经过了几次尝试决定使用Mysql存储过程和Mybatis调用存储过程的方式来实现,这样做的好处是直接得到了我们想要得数据结构,不需要再进行处理,并且存储过程可以封装复杂的sql语句,供外部直接调用,解决了mybatis无法处理复杂sql语句的问题。
2、动态行转列sql语句
2.1sql语句
SET @EE='';
SET @str_tmp='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(plan.plan_date=\'',plan_date,'\'',',plan.num,0)) AS `',plan_date,'`,') AS aa
INTO @str_tmp FROM (SELECT DISTINCT plan_date FROM plan where plan_date between '2022-07-01' and '2022-07-03') A ORDER BY LENGTH(aa) DESC LIMIT 1;
SET @QQ=CONCAT('SELECT base_id,base.product_code,base.product_name, ',LEFT(@str_tmp,CHAR_LENGTH(@str_tmp)-1),' FROM plan ,base GROUP BY base_id');
PREPARE stmt FROM @QQ;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;
2.2sql语句解析
SET @EE='';
SET @str_tmp='';
set可以定义系统变量和用户变量,用户变量定义可以使用set和select两种,
用户变量以‘@’开头,任意字符串命名。
使用set定义可以使用‘=’和‘:=’,使用select只能使用‘:=’
此sql语句定义了@EE和@str_tmp
SELECT @EE:=CONCAT(@EE,'SUM(IF(plan.plan_date=\'',plan_date,'\'',',plan.num,0)) AS `',plan_date,'`,') AS aa
INTO @str_tmp FROM (SELECT DISTINCT plan_date FROM plan where plan_date between '2022-07-01' and '2022-07-03') A ORDER BY LENGTH(aa) DESC LIMIT 1;
复杂sql语句先将主干分析出来,上面sql语句本质上还是select 字段 from 某某表
字段为@EE:=CONCAT(@EE,'SUM(IF(plan_date=\'',plan_date,'\'',',num,0)) AS `',plan_date,'`,') AS aa
INTO @str_tmp
表为SELECT DISTINCT plan_date FROM plan where plan_date between '2022-07-01' and '2022-07-03'查询出来的临时表,
@EE变量等于由CONCAT函数拼接成的字符串并放到@str_tmp变量中,最后再进行排序并限制为一行
CONCAT函数用于字符串的拼接
通过select @str_tmp可以看到拼接的结果为:
SET @QQ=CONCAT('SELECT base_id,base.product_code,base.product_name, ',LEFT(@str_tmp,CHAR_LENGTH(@str_tmp)-1),' FROM plan ,base GROUP BY base_id');
此sql语句同上,定义变量@QQ,其值是通过CONCAT函数得到字符串,通过select @QQ可以看到其结果为:
PREPARE stmt FROM @QQ;
预备一个语句 stmt
EXECUTE stmt;
执行 stmt 语句
DEALLOCATE PREPARE stmt;
释放执行中所使用的数据库资源
PREPARE,EXECUTE,DEALLOCATE PREPARE,是一个流程,可以减少每次执行SQL的语法分析
3、mysql构建存储过程
上面这样复杂的sql语句在mybatis中无法使用,所以要将复杂的sql语句封装到存储过程中。封装过程如下:
第一步:在navicat选择函数,然后点击新建函数
第二步:选择过程,并对过程命名,在这里命名为getInfo,然后点击下一步
第三步:输入参数模式、参数名称、参数类型,然后点击完成
参数模式包括三种:IN OUT INOUT
IN表示输入参数 OUT表示输出参数 INOUT既表示输入参数又表示输出参数
注意:在这里由于动态行转列输出的参数并不是固定的,所以这里只需要声明输入参数即可。
第四步:将sql语句放在BEGIN 与 END之间,并且注意要将sql语句中固定的条件换成第三步输入的参数,然后点击保存,运行一下检查是否成功
注意:sql语句中一定要注意空格,标点符号问题,仔细比对,检查
4、mybatis调用mysql存储过程
Mapper层:使用LIstanbul<Map>进行接收
xml:
语法:
call 存储过程名称(#{参数名称,mode=参数模式},#{参数名称,mode=参数模式})
注意,在mybatis中参数模式全部要大写,否则编译时会报错,另外,看到一些语法为
{call 存储过程名称(#{参数名称,mode=参数模式},#{参数名称,mode=参数模式})} 在外面加了大括号,不知道什么原因,我没有成功,若不加大括号不成功可以试试加上。
statementType的值是CALLABLE,告诉mybatis将要执行的是存储过程
resultType表示返回类型