使用更新联接表时，操作数应包含1列

Question

我正试图通过以下组合两个查询来更新表

 UPDATE result, games_played
      SET result.precentage_correct =
(
SELECT user_id, 100*SUM(n_win)/SUM(n_total) AS pct_win FROM
(SELECT user_id, COUNT(user_id) AS n_win,
  NULL AS n_total
  FROM games_played
  WHERE winner != 'n'
  AND game = 1
  GROUP BY user_id
  UNION SELECT user_id, NULL AS n_win,
  COUNT(user_id) AS n_total
  FROM games_played
  WHERE game = 1
  GROUP BY user_id
) AS counts
GROUP BY counts.user_id
)
WHERE result.user_id = games_played.user_id

然而，我得到错误

Operand should contain 1 column(s)

有人知道我做错了什么吗。。。我可以选择结果作为新表

SQL fiddlehttp://sqlfiddle.com/#！2/5374e6/1

Answer 1

尝试在子查询中添加筛选条件。您在注释中提到的错误"子查询返回多行"意味着子查询（您命名为as计数的子查询）返回了多个结果。类似这样的东西：

UPDATE result, games_played
      SET result.precentage_correct =
(
SELECT 100*SUM(n_win)/SUM(n_total) AS pct_win FROM
(SELECT user_id, COUNT(user_id) AS n_win,
  NULL AS n_total
  FROM games_played
  WHERE winner != 'n'
  AND game = 1
  GROUP BY user_id
  UNION SELECT user_id, NULL AS n_win,
  COUNT(user_id) AS n_total
  FROM games_played
  WHERE game = 1
  GROUP BY user_id
) AS counts
GROUP BY counts.user_id
HAVING counts.user_id = result.user_id
)
WHERE result.user_id = games_played.user_id

Answer 2

我不太确定查询的逻辑，但您可以通过在源表列表中对查询进行别名处理，将查询实际用作表来修复语法错误。

http://sqlfiddle.com/#！2/436eb/1

UPDATE result, games_played,
  (SELECT t1.user_id, 100*SUM(t1.n_win)/SUM(t1.n_total) AS pct_win FROM
   (SELECT user_id, COUNT(user_id) AS n_win, NULL AS n_total
     FROM games_played
     WHERE winner != 'n' AND game = 1
     GROUP BY user_id
    UNION 
    SELECT user_id, NULL AS n_win, COUNT(user_id) AS n_total
     FROM games_played
     WHERE game = 1
     GROUP BY user_id
   ) AS t1
  ) AS counts
SET result.precentage_correct = counts.pct_win
WHERE result.user_id = games_played.user_id

Answer 3

您可以简化UPDATE。请参阅SQL Fiddle:

UPDATE result
SET result.precentage_correct =
    ( SELECT 100*SUM(winner <> 'n')/COUNT(*) AS pct_win
      FROM games_played
      WHERE game = 1
        AND result.user_id = games_played.user_id
    ) ;

或者使用更像您的逻辑的查询。这甚至更好，因为（与前一个不同）它不会UPDATE整个result表（对于那些没有玩过游戏的用户，为null），而只会games_played表中至少有一个结果的用户。在SQL-Fiddle-2 上测试

UPDATE result AS r
  JOIN
    ( SELECT user_id, 
             100*SUM(winner <> 'n')/COUNT(*) AS pct_win
      FROM games_played
      WHERE game = 1
      GROUP BY user_id
    ) AS gp
    ON r.user_id = gp.user_id
SET r.precentage_correct = gp.pct_win ;