我正在尝试建立一个表,该表总结“缓存”表中的数据。
因为我计划每隔几分钟运行一次聚合查询(通过cron或按事件,并不重要),并且为了避免重复的行,我希望摘要表中已经存在的行将被更新(我已经配置了唯一键),因此我的“缓存”表中的值将与现有值集成在一起。
这是我的查询:
INSERT INTO `ReportsSummary`
(`date`,
`txtID`,
`webID`,
`GEO`,
`OSS`,
`impressions`,
`clicks`)
SELECT DATE_FORMAT(date ,'%Y-%m-%d'),txtID,webID, GEO, OSS,
SUM( if(actionType = '1',1,0) ) as add_impressions,
SUM( if(actionType = '2',1,0) ) as add_clicks
FROM `CacheReports` as t GROUP BY txtID,webID,GEO,OSS
ON DUPLICATE KEY UPDATE
impressions = impressions + (`t`.`add_impressions`),
clicks = clicks + (`t`.`add_clicks`);
干杯
最佳答案
表t(CacheReports)中没有add_impressions和add_clicks列-包含它们的是您的集合。使用以下更正的查询:
INSERT INTO `ReportsSummary`
(`date`,
`txtID`,
`webID`,
`GEO`,
`OSS`,
`impressions`,
`clicks`)
SELECT * FROM (
SELECT DATE_FORMAT(date ,'%Y-%m-%d'),txtID,webID, GEO, OSS,
SUM( if(actionType = '1',1,0) ) as add_impressions,
SUM( if(actionType = '2',1,0) ) as add_clicks
FROM `CacheReports` as t GROUP BY txtID,webID,GEO,OSS) AS `u`
ON DUPLICATE KEY UPDATE
impressions = impressions + `u`.`add_impressions`,
clicks = clicks + `u`.`add_clicks`;
关于mysql - MySQL DUPLICATE KEY UPDATE + SUM函数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/19888219/