mysql定义查询结果_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

上面的执行太慢，没法使用！！！