MySQL-如何存储GROUP BY的结果以用于ON DUPLICATE KEY UPDATE [英] MySQL - how to store results of GROUP BY for use in ON DUPLICATE KEY UPDATE
本文介绍了MySQL-如何存储GROUP BY的结果以用于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屋!
查看全文