mysql - 使用更新连接表时，操作数应包含 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 fiddle http://sqlfiddle.com/#!2/5374e6/1

Answer 1

尝试在您的子查询中添加过滤条件。您在评论中提到的错误“子查询返回多于 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