mysql - 将一行的结果调整为其他行

Question

我怎样才能以这种方式编写查询，使 CASE WHEN - 语句的结果适应每一行。这样每一行的结果都是 5。非常感谢!

CREATE TABLE DATA
(`Person` CHAR(4),
 `Apples` INT(1),
 `Tomatoes` INT(1),
 `Result` INT(1)
  );

 INSERT INTO DATA
 (Person, Apples, Tomatoes)
  VALUES ('Mark' , 1, 2),
    ('Sepp',  2, 3),
    ('Carl',  3, 1);

 UPDATE  DATA
 SET `Result` = CASE WHEN (`Person` =  'Sepp') THEN (`Apples` + `Tomatoes`)  END;

Table of result as it should be

SQL fiddle demonstration

Answer 1

如果您希望所有行都从 Sepps 行获取值，您可以使用子查询来实现。

“正常”方法是这样做:

UPDATE DATA
SET Result = (SELECT Apples + Tomatoes FROM DATA WHERE Person =  'Sepp')

但这很可能会给您带来 MySQL 错误(无法指定更新目标表)，解决方法是在查询中引入另一个级别，强制使用临时表，像这样:

UPDATE DATA
 SET Result = (
   SELECT Value FROM (
     SELECT Apples + Tomatoes AS Value 
     FROM DATA WHERE Person =  'Sepp'
   ) t
 );