MySQL-如何存储GROUP BY的结果以用于ON DUPLICATE KEY UPDATE [英] MySQL - how to store results of GROUP BY for use in ON DUPLICATE KEY UPDATE

问题描述

如何解决以下查询？我将SELECT查询的结果别名到表 tmp 中的尝试无效。

How do I fix the following query? My attempt to alias the result of the SELECT query into table tmp does not work.

INSERT INTO vote_counts (list_id, node_id, num_down_votes) 
  (SELECT vl.list_id, vl.node_id, COUNT(*) AS c
    FROM vote_logs vl 
   WHERE vl.node_id=2442703 AND vl.agree_type='DOWN_VOTE' 
GROUP BY vl.list_id) tmp
ON DUPLICATE KEY UPDATE num_down_votes = tmp.c;

vote_counts 表具有唯一的键约束在（list_id，node_id）上。

The vote_counts table has a unique key constraint on (list_id, node_id).

推荐答案

如果我不是错误的，这应该可行：

If I'm not mistaken, this should work:

INSERT INTO vote_counts (list_id, node_id, num_down_votes) 
SELECT vl.list_id, vl.node_id, COUNT(*) AS c
FROM vote_logs vl 
WHERE vl.node_id=2442703 AND vl.agree_type='DOWN_VOTE' 
GROUP BY vl.list_id
ON DUPLICATE KEY UPDATE num_down_votes = vl.c;

如果 vl.c 不起作用，您可能需要使用 Count（vl。*），因为它是别名-不确定，必须对其进行测试。

If vl.c doesn't work, you may need to use Count(vl.*) since it's an alias -- not sure, would have to test it.

-编辑

也许使用子查询将解决此问题：

Perhaps using a subquery will resolve this issue:

INSERT INTO vote_counts (list_id, node_id, num_down_votes) 
SELECT list_id, node_id, c
FROM (
    SELECT vl.list_id, vl.node_id, COUNT(*) AS c
    FROM vote_logs vl 
    WHERE vl.node_id=2442703 AND vl.agree_type='DOWN_VOTE' 
    GROUP BY vl.list_id
) tempTable
ON DUPLICATE KEY UPDATE num_down_votes = tempTable.c;

祝你好运。

这篇关于MySQL-如何存储GROUP BY的结果以用于ON DUPLICATE KEY UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！