如何求和然后删除重复的字段 MySql? [英] How can I sum and then remove duplicate fields MySql?

问题描述

很抱歉，如果结果是重复的，但我很难找到确切的答案...

Sorry if this turns out to be duplicate but I am having a hard time finding the exact answer...

我有一个包含email"(varchar)和clicks"(int)的表格.

I have table that contains 'email'(varchar) and 'clicks'(int).

电子邮件字段包含电子邮件值的重复实例.我想删除重复项并仅保留一个实例，但还要汇总在该给定电子邮件实例的点击次数"中找到的所有值，并更新剩余记录以反映这一点.

The email field contains duplicate instances of email values. I would like to remove the duplicates and keep just one instance but also sum all values found in 'clicks' for that given email instance and update the remaining record to reflect that.

我可以使用 distinct 和 group by 来查看我所关注的记录，这是我难倒的总和和更新部分.

I can use distinct and group by to get a view of the records I am after, its the sum and update part I am stumped with.

谢谢.

推荐答案

我不认为你需要在这里使用 DISTINCT，而是你可以对每个 电子邮件进行分组代码> 值，随着您进行点击次数的总和:

I don't think you need to use DISTINCT here, but rather you can just group on each email value, taking the sum of clicks as you go along:

SELECT email, SUM(clicks) AS clickSum
FROM yourTable
GROUP BY email

当您提到剩余"记录时，您忽略了重复记录是如何被删除的.有选择地删除除一个重复记录之外的所有记录将是一大堆工作.我可能会通过将上述查询插入新表、删除旧表并将新表重命名为旧表来解决此问题:

When you mentioned "remaining" record, you neglected to mention how the duplicate records ever got deleted. It would be a bunch of work to selectively remove all but one duplicate record. I might go about this by just inserting the above query into a new table, deleting the old table, and renaming the new one to the old one:

CREATE TABLE yourNewTable (`email` varchar(255), `clicks` int);
INSERT INTO yourNewTable (`email`, `clicks`)
SELECT email, SUM(clicks)
FROM yourTable
GROUP BY email

DROP TABLE yourTable
ALTER TABLE yourNewTable RENAME TO yourTable

这篇关于如何求和然后删除重复的字段 MySql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！