mysql - 用常量值更新表

Question

我的表中有 7 列(s0、s1、s2、s3、s4、s5、s6)，我想执行以下查询:

UPDATE myTable SET s0=ROUND(s0/(s0+s1+s2+s3+s4+s5+s6)*100)/100,
  s1=ROUND(s1/(s0+s1+s2+s3+s4+s5+s6)*100)/100, 
  s2=ROUND(s2/(s0+s1+s2+s3+s4+s5+s6)*100)/100,
  s3=ROUND(s3/(s0+s1+s2+s3+s4+s5+s6)*100)/100, 
  s4=ROUND(s4/(s0+s1+s2+s3+s4+s5+s6)*100)/100, 
  s5=ROUND(s5/(s0+s1+s2+s3+s4+s5+s6)*100)/100, 
  s6=ROUND(s6/(s0+s1+s2+s3+s4+s5+s6)*100)/100;

问题是mysql更新s1，然后计算s2等

如何在 sql 请求中为每一行修复值 (s0+s1+s2+s3+s4+s5+s6)？

Answer 1

我相信它也应该完成这项工作(假设表有一个主键):

UPDATE myTable a
INNER JOIN 
(
  SELECT ROUND(s0/(s0+s1+s2+s3+s4+s5+s6)*100)/100 AS new_s0,
  ROUND(s1/(s0+s1+s2+s3+s4+s5+s6)*100)/100 AS new_s1, 
  ROUND(s2/(s0+s1+s2+s3+s4+s5+s6)*100)/100 AS new_s2,
  ROUND(s3/(s0+s1+s2+s3+s4+s5+s6)*100)/100 AS new_s3, 
  ROUND(s4/(s0+s1+s2+s3+s4+s5+s6)*100)/100 AS new_s4, 
  ROUND(s5/(s0+s1+s2+s3+s4+s5+s6)*100)/100 AS new_s5, 
  ROUND(s6/(s0+s1+s2+s3+s4+s5+s6)*100)/100 AS new_s6,
  pk_column 
  FROM myTable
)b ON (b.pk_column = a.pk_column)
SET a.s0 = b.new_s0, ....