mysql定义查询结果_mysql 变量定义 sql查询

港控/mmm° 2023-01-11 08:28 174阅读 0赞

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

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

mysql定义查询结果_mysql 变量定义 sql查询

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关自定义sql查询

相关 mysql自定义结果_MySQL自定义函数调用不出结果

相关 mysql定义查询结果_mysql 变量定义 sql查询

相关 mysql自定义变量，自定义变量显示行号、简化查询、赋值

相关 mysql查询结果分页_分页MySQL查询结果

相关 MySQL对查询结果排序

相关 Hibernate 自定义查询sql 并使用自定义对象接收查询结果

相关 MySQL合并查询结果

相关 mysql查询结果添加序号

相关 mysql in查询结果顺序