SP没有看,给你个SQL示例,已经测试通过lsb1:字段ID,内容1-100000SELECT `product_id`,CONCAT(IF(LENGTH(AA1)>0,'&',''),AA) FROM ( SELECT *,SUBSTRING_INDEX( SUBSTRING_INDEX(A1.NA,'|',b.id),'|',-1) AS AA FROM (SELECT AA1,`product_id`, REPLACE(REPLACE(REPLACE(`product_rule`,CONCAT(AA1,'&'),''),')',''),'(','') AS NA FROM ( SELECT *,IF(INSTR(A1.`product_rule`,'&(')>0,LEFT(A1.`product_rule`,INSTR(A1.`product_rule`,'&(')-1),'') AS AA1 FROM `cfg_product` A1 WHERE INSTR(A1.`product_rule`,'|')>0) AA) a1 LEFT JOIN lsb1 b ON (LENGTH(A1.NA)-LENGTH(REPLACE(A1.NA,'|','')))+1>=b.id) PP ;
你的测试数据和表结构是sql 可以贴下吗
Query : SELECT `product_id`,CONCAT(IF(LENGTH(AA1)>0,'&',''),AA) FROM ( SELECT *,SUBSTRING_INDEX(SUBSTRING_INDEX(A1.NA,'|',b.id),'|',-1...Error Code : 1146 Table 'test.lsb1' doesn't existExecution Time : 00:00:00:000 Transfer Time : 00:00:00:000 Total Time : 00:00:00:000 ---------------------------------------------------
注意看看4楼的说明 lsb1:字段ID,内容1-100000
要求格式固定,比如 (恒泰|楚留)&香米-> 香米&(恒泰|楚留)否则要专门曾这种情况做处理SELECT *,`product_id`,CONCAT(aa1,IF(LENGTH(aa1)>0,'&',''),AA) AS aa1 FROM ( SELECT *,SUBSTRING_INDEX( SUBSTRING_INDEX(A1.NA,'|',b.id),'|',-1) AS AA FROM (SELECT AA1,`product_id`, IF(LENGTH(aa1)>0, REPLACE(REPLACE(REPLACE(`product_rule`,CONCAT(AA1,'&'),''),')',''),'(',''),`product_rule`) AS NA FROM ( SELECT *,IF(INSTR(A1.`product_rule`,'&(')>0,LEFT(A1.`product_rule`,INSTR(A1.`product_rule`,'&(')-1),'') AS AA1 FROM `cfg_product` A1 WHERE INSTR(A1.`product_rule`,'|')>0) AA) a1 LEFT JOIN lsb1 b ON (LENGTH(A1.NA)-LENGTH(REPLACE(A1.NA,'|','')))+1>=b.id) PP
LSB1,字段ID,值1、2、3、4、510000 这种 (恒泰|楚留)&香米 处理好了 SELECT *,`product_id`, CONCAT( IF(INSTR(`product_rule`,'|')<INSTR(`product_rule`,'&'),'',aa1), IF(LENGTH(aa1)>0 AND INSTR(`product_rule`,'|')>INSTR(`product_rule`,'&'),'&',''),AA, IF(INSTR(`product_rule`,'|')<INSTR(`product_rule`,'&'),CONCAT('&',aa1),'') ) AS aa2 FROM ( SELECT *,SUBSTRING_INDEX( SUBSTRING_INDEX(A1.NA,'|',b.id),'|',-1) AS AA FROM (SELECT AA1,`product_id`,`product_rule`, IF(LENGTH(aa1)>0, REPLACE(REPLACE(REPLACE(`product_rule`, CONCAT( IF(INSTR(`product_rule`,'|')<INSTR(`product_rule`,'&'),'&',''),aa1, IF(INSTR(`product_rule`,'|')<INSTR(`product_rule`,'&'),'','&')),''),')',''),'(',''), `product_rule`) AS NA FROM ( SELECT *, IF(INSTR(A1.`product_rule`,'&(')>0,LEFT(A1.`product_rule`,INSTR(A1.`product_rule`,'&(')-1), IF(INSTR(A1.`product_rule`,'|')<INSTR(A1.`product_rule`,'&'),RIGHT(A1.`product_rule`,CHAR_LENGTH(A1.`product_rule`)-INSTR(A1.`product_rule`,'&')), '')) AS AA1 FROM `cfg_product` A1 WHERE INSTR(A1.`product_rule`,'|')>0) AA) a1 LEFT JOIN lsb1 b ON (LENGTH(A1.NA)-LENGTH(REPLACE(A1.NA,'|','')))+1>=b.id) PP
SELECT *,SUBSTRING_INDEX(
SUBSTRING_INDEX(A1.NA,'|',b.id),'|',-1) AS AA FROM (SELECT AA1,`product_id`,
REPLACE(REPLACE(REPLACE(`product_rule`,CONCAT(AA1,'&'),''),')',''),'(','') AS NA FROM (
SELECT *,IF(INSTR(A1.`product_rule`,'&(')>0,LEFT(A1.`product_rule`,INSTR(A1.`product_rule`,'&(')-1),'') AS AA1
FROM `cfg_product` A1
WHERE INSTR(A1.`product_rule`,'|')>0) AA) a1 LEFT JOIN lsb1 b
ON (LENGTH(A1.NA)-LENGTH(REPLACE(A1.NA,'|','')))+1>=b.id) PP
;
Query : SELECT `product_id`,CONCAT(IF(LENGTH(AA1)>0,'&',''),AA) FROM ( SELECT *,SUBSTRING_INDEX(SUBSTRING_INDEX(A1.NA,'|',b.id),'|',-1...Error Code : 1146
Table 'test.lsb1' doesn't existExecution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
---------------------------------------------------
lsb1:字段ID,内容1-100000
(恒泰|楚留)&香米->
香米&(恒泰|楚留)否则要专门曾这种情况做处理SELECT *,`product_id`,CONCAT(aa1,IF(LENGTH(aa1)>0,'&',''),AA) AS aa1 FROM (
SELECT *,SUBSTRING_INDEX(
SUBSTRING_INDEX(A1.NA,'|',b.id),'|',-1) AS AA FROM (SELECT AA1,`product_id`,
IF(LENGTH(aa1)>0,
REPLACE(REPLACE(REPLACE(`product_rule`,CONCAT(AA1,'&'),''),')',''),'(',''),`product_rule`) AS NA FROM (
SELECT *,IF(INSTR(A1.`product_rule`,'&(')>0,LEFT(A1.`product_rule`,INSTR(A1.`product_rule`,'&(')-1),'') AS AA1
FROM `cfg_product` A1
WHERE INSTR(A1.`product_rule`,'|')>0) AA) a1 LEFT JOIN lsb1 b
ON (LENGTH(A1.NA)-LENGTH(REPLACE(A1.NA,'|','')))+1>=b.id) PP
这种 (恒泰|楚留)&香米 处理好了
SELECT *,`product_id`,
CONCAT(
IF(INSTR(`product_rule`,'|')<INSTR(`product_rule`,'&'),'',aa1),
IF(LENGTH(aa1)>0 AND INSTR(`product_rule`,'|')>INSTR(`product_rule`,'&'),'&',''),AA,
IF(INSTR(`product_rule`,'|')<INSTR(`product_rule`,'&'),CONCAT('&',aa1),'')
) AS aa2
FROM (
SELECT *,SUBSTRING_INDEX(
SUBSTRING_INDEX(A1.NA,'|',b.id),'|',-1) AS AA FROM (SELECT AA1,`product_id`,`product_rule`,
IF(LENGTH(aa1)>0,
REPLACE(REPLACE(REPLACE(`product_rule`,
CONCAT(
IF(INSTR(`product_rule`,'|')<INSTR(`product_rule`,'&'),'&',''),aa1,
IF(INSTR(`product_rule`,'|')<INSTR(`product_rule`,'&'),'','&')),''),')',''),'(',''),
`product_rule`) AS NA FROM (
SELECT *,
IF(INSTR(A1.`product_rule`,'&(')>0,LEFT(A1.`product_rule`,INSTR(A1.`product_rule`,'&(')-1),
IF(INSTR(A1.`product_rule`,'|')<INSTR(A1.`product_rule`,'&'),RIGHT(A1.`product_rule`,CHAR_LENGTH(A1.`product_rule`)-INSTR(A1.`product_rule`,'&')),
'')) AS AA1
FROM `cfg_product` A1
WHERE INSTR(A1.`product_rule`,'|')>0) AA) a1 LEFT JOIN lsb1 b
ON (LENGTH(A1.NA)-LENGTH(REPLACE(A1.NA,'|','')))+1>=b.id) PP