在副本上使用组函数无效

Question

我试图从临时表更新表，并获得错误无效使用组函数。如果我去掉on duplicate key update部分它不会给出那个错误。我不知道该怎么办

insert Finalized_Data.Transaction_Details(Store,Ticket,SalespersonCode,ArchSupportsSold,class) 
select Store,Ticket,SalespersonCode,count(class),class 
FROM POS_Data_Import.auto_import_ExportPurchaseDetails as example
group by store,ticket,substring(class,0,7) 
having class like 'ARCH SUPPORTS%'
on duplicate key update ArchSupportsSold=count(example.class);

Answer 1

只能在SELECT()查询中使用聚合函数。在INSERT查询本身中没有进行聚合。

如果您想获得在没有重复的情况下插入到列中的值，请使用VALUES()函数:

ON DUPLICATE KEY UPDATE ArchSupportsSold = VALUES(ArchSupportsSold)

VALUES()函数在MySQL 8.0.20中已弃用(参见文档)。从这个版本开始，您应该使用子查询来分配列别名，并引用它。

insert Finalized_Data.Transaction_Details(Store,Ticket,SalespersonCode,ArchSupportsSold,class) 
SELECT * FROM (
select Store,Ticket,SalespersonCode,count(class) AS newSold ,class 
FROM POS_Data_Import.auto_import_ExportPurchaseDetails as example
group by store,ticket,substring(class,0,7) 
having class like 'ARCH SUPPORTS%'
) AS x
on duplicate key update ArchSupportsSold = newSold;