你可以在ON DUPLICATE KEY语句中使用聚合值吗？

Question

我正在尝试编写类似于以下查询的语句：

INSERT INTO SomeTable(field1, field2) 
SELECT 'blah' AS field1, 
        MAX(AnotherTable.number) AS field2
FROM AnotherTable
ON DUPLICATE KEY 
UPDATE field1= 'blah', field2 = MAX(AnotherTable.number)

我遇到了错误代码：1111，无效的组函数使用。

阅读 MySql 文档：

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

"感兴趣的行是："

"在ON DUPLICATE KEY UPDATE的值部分中，您可以引用其他表中的列，只要您不在SELECT部分使用GROUP BY。一个副作用是您必须在值部分限定非唯一列名。"

"这是我看到的问题吗？我没有在Select语句中特别使用GROUP BY，但通过使用聚合函数（Max），那么我可能会隐式地分组。"

"如果有人确定我是否隐式地执行了GROUP BY或者是否有其他方法可以得到所需的结果，我将非常感激。"

- rioubenson

Answer 1

我知道我正在回答自己的问题，但是...

最终让它工作了（感谢：一个坏链接）

INSERT INTO SomeTable(field1, field2) 
SELECT 'blah' AS field1, 
    MAX(AnotherTable.number) AS field2
FROM AnotherTable
ON DUPLICATE KEY 
UPDATE field2 = values(field2)

- rioubenson

---

啊，值的事情，当然。我忘了那个。+1 给你 :) - fancyPants

注意，在将内容插入表中时，应将列名传递到values()函数中，而不是聚合字段的名称。@user1491016，如果将MAX（）字段重命名为max_field2，可能会更清晰明了。无论如何，你让我朝着正确的方向前进，感谢你提供的解决方案 :) - adavea

这在MySQL 8.xx中如何工作？我收到警告1287“'VALUES function'已弃用，并将在未来的版本中删除。请使用别名（INSERT INTO ... VALUES (...) AS alias）并将ON DUPLICATE KEY UPDATE子句中的VALUES(col)替换为alias.col”。但在这种语句中，我们有一个子查询... - rantanplan

Answer 2

请尝试一下这个是否有效：

INSERT INTO SomeTable(field1, field2) 
SELECT * FROM (
SELECT * FROM (
SELECT 'blah' AS field1, 
        MAX(AnotherTable.number) AS field2
FROM AnotherTable
) sq
) sq2
ON DUPLICATE KEY 
UPDATE field1= 'blah', field2 = sq2.field2

不确定是否需要两次子查询。我通常使用这种方法来规避MySQL的限制，即无法使用从同一表中读取的值更新表。不确定这里是否适用。

- fancyPants

---

遗憾的是，那也不起作用。它无法识别AnotherTable.field2。我尝试过对选择进行别名，但MySql也无法理解。 - rioubenson

修改了我的回答，请再试一次。 - fancyPants

感谢fancyPants。在搜索了谷歌之后，我找到了这个链接：http://www.getrouty.com/mysql-insert-select-on-duplicate-update-using-aggregate-functions/，如果我添加值，它似乎可以工作。我将在下面添加SQL。 - rioubenson