mysql 变量定义 sql查询

SET @idnoStr:='"idNo":"';
SELECT LOCATE(@idnoStr, param_array),
LOCATE('",', param_array,LOCATE('"idNo":"', param_array)),
SUBSTR(param_array,LOCATE('"idNo":"', param_array),
LOCATE('",', param_array,LOCATE('"idNo":"', param_array))-LOCATE('"idNo":"', param_array)
)
param_array FROM t_gl_adapter_param_input
 where creat_time BETWEEN '2018-12-01 16:37:41' and '2019-01-01 23:59:59'
and service_type='qianhaiHaoxinduHcService'
and param_array like '{"app%'

  见第一行;

分析号码,并得出结果:

-- BEGIN
-- DECLARE idnoStr VARCHAR(8);
SET @idnoStr:='"idNo":"';
SELECT aa.serial_num,tmp_seqid_qh.*,d.content FROM 
(SELECT  serial_num, 
SUBSTR(param_array,
LOCATE(@idnoStr, param_array)+8,
LOCATE('",', param_array,LOCATE(@idnoStr, param_array))-LOCATE(@idnoStr, param_array)-8
)
idno FROM t_gl_adapter_param_input 
 where creat_time BETWEEN '2018-12-01 16:37:41' and '2019-01-01 23:59:59'
and service_type='qianhaiHaoxinduHcService'
and param_array like '{"app%'
) aa INNER JOIN tmp_seqid_qh on tmp_seqid_qh.idno=aa.idno
left join t_gl_adapter_param_record_content d on aa.serial_num = d.serial_num 
 HAVING d.content LIKE '{"bat%'


-- and param_array like (select idno from tmp_seqid_qh)	
-- END

  步骤:

一、导入临时表

1、建表,在ext 菜单中建立;

2、在测试类中生产批量插入语句;

insert into tmp_seqid_qh(loanid,seqid,idno)values('NXJ18060111117ZG5U','1528091231073490S180F4F2C4523762','230307197101014010'); 

3、导入在当前表菜单中导入;

4、写sql,在工具里导出XML;

5、用测试类分析xml并生成Excel数据语句;粘贴到Excel;

SET @idnoStr:='"sequence_id":"';
SET @len:=LENGTH(@idnoStr);
select 
a.serial_num, 
a.seq_id, 
d.content as record_param
from 
( 
SELECT 
serial_num, 
SUBSTR(
		param_array,
		LOCATE(@idnoStr, param_array) + @len,
		LOCATE(
			'",',
			param_array,
			LOCATE(@idnoStr, param_array)
		) - LOCATE(@idnoStr, param_array) - @len
) seq_id 
FROM t_gl_adapter_param_input 

where 
service_type = 'tongdunRuleDetailAnalysisHcService' 
and creat_time BETWEEN '2018-01-01' and '2018-07-01'

and SUBSTR(
		param_array,
		LOCATE(@idnoStr, param_array) + @len,
		LOCATE(
			'",',
			param_array,
			LOCATE(@idnoStr, param_array)
		) - LOCATE(@idnoStr, param_array) - @len
) in (SELECT tmp_seqid_qh.seqid FROM tmp_seqid_qh WHERE
 LENGTH(tmp_seqid_qh.seqid)!='')

order by creat_time desc -- limit 500 
) a  
left join t_gl_adapter_param_record_content d on a.serial_num = d.serial_num 

  上面的执行太慢,没法使用!!!

转载于:https://www.cnblogs.com/hoge66/p/10276558.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值