2021-10-29

将字符串’200,300,400,'拆分多行，循环数据批量更新到对应表jcb中的jgz字段中去，使用存储过程实现该操作
字段注释：
– cnd 浓度 tj 体积
– zl 质量 cnd2 浓度2
– tj2 体积2 zl2 质量2
– x1 含量1 x2 含量2
– x3 含量3 jg 结果 wc 误差
CREATE PROCEDURE js(IN nd VARCHAR(255))
BEGIN
DECLARE C,V,m DECIMAL(12,2);
DECLARE C1,V1,m1 DECIMAL(12,2);
DECLARE a,b,d,e DECIMAL(12,2);
DECLARE i,num INT DEFAULT 0;
DECLARE ypjcid INT;

SELECT cnd,tj,zl,cnd2,tj2,zl2 INTO C,V,m,C1,V1,m1
FROM TABLE k WHERE bh=nd;
SET b = (CV1000)/(m10001000);
SET a = (C1V11000)/(m110001000);
SET d = (a+b)/2;
SET e = (a-d)/d;
UPDATE TABLE k SET x1=b, x2=a, x3=d, jg=a, wc=e WHERE bh=nd;
SELECT COUNT(*) INTO num
FROM TABLE k JOIN mysql.help_topic b ON b.help_topic_id < (LENGTH(k.jg) - LENGTH(REPLACE(k.jg,’,’,’’))+1)
WHERE a.bh = nd AND SUBSTRING_INDEX(SUBSTRING_INDEX(k.jg,’,’,b.help_topic_id+1),’,’,-1)!=’’;
WHILE i <num DO
SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(k.jg,’,’,b.help_topic_id+1),’,’,-1) INTO ypjcid
FROM TABLE k JOIN mysql.help_topic b ON b.help_topic_id < (LENGTH(k.jg) - LENGTH(REPLACE(k.jg,’,’,’’))+1)
WHERE a.bh = nd AND SUBSTRING_INDEX(SUBSTRING_INDEX(k.jg,’,’,b.help_topic_id+1),’,’,-1)!=’’ LIMIT i,1;
UPDATE jcb SET jcb.jgz=a WHERE jcb.id = ypjcid;
SET i = i+1;
END WHILE;