问题是在重复键子句中不能使用任何分组函数(如COUNT.但是，有一个简单的解决这个问题的方法.您只需将 COUNT(crime_id) 调用的结果分配给一个变量，您可以在重复的键子句中使用该变量.然后您的插入语句将看起来像这样:

The problem is that in the duplicate key clauses you cannot use any grouping functions (such as COUNT. However, there is an easy way around this problem. You just assign the result of the COUNT(crime_id) call to a variable, which you can use in the duplicate key clauses. Your insert statement would then look like this:

INSERT INTO `simple_crimecount` (`date` , `city` , `crimecount`)(
    SELECT 
        `date`, 
        `city`,
        @determined_crimecount := count(`crime_id`) AS `determined_crimecount`
    FROM `big_log_of_crimes`
    GROUP BY `date`, `city`
) ON DUPLICATE KEY UPDATE `crimecount` = @determined_crimecount;

我创建了一个 SQL Fiddle，向您展示它是如何工作的:SQL-Fiddle

I have create an SQL Fiddle that shows you how it works: SQL-Fiddle

您也可以使用 UPDATE crimecount = VALUES(crimecount) 并且没有变量:

You could also use UPDATE crimecount = VALUES(crimecount) and no variables:

INSERT INTO `simple_crimecount` (`date` , `city` , `crimecount`)(
    SELECT 
        `date`, 
        `city`,
        count(`crime_id`) AS `determined_crimecount`
    FROM `big_log_of_crimes`
    GROUP BY `date`, `city`
) ON DUPLICATE KEY UPDATE `crimecount` = VALUES(crimecount);

请参阅 SQL-Fiddle-2

See the SQL-Fiddle-2