更新时间:2023-02-26 10:42:12
问题是,在重复的关键字段中,您不能使用任何分组函数(例如 COUNT
),但是有一个简单的方法这个问题,您只需将 COUNT(crime_id)
调用的结果分配给您可以在重复键子句中使用的变量。 insert语句将会如下所示:
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;
我h ave创建一个SQL小提琴,向您显示如何运作: 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