MySQL 中部分匹配的字段分组

发布于 2024-08-18 08:14:40 字数 771 浏览 7 评论 0原文

我试图返回用户表中的重复记录，其中字段仅部分匹配，并且匹配的字段内容是任意的。我不确定我是否解释得很好，所以这是我可能运行的查询，以通过某个唯一字段获取重复成员：

SELECT MAX(id)
FROM members
WHERE 1
GROUP BY some_unique_field
HAVING COUNT(some_unique_field) > 1

我想将相同的想法应用于电子邮件字段，但不幸的是我们的电子邮件字段可以包含多个电子邮件，以逗号分隔。例如，我希望成员的电子邮件设置为“[电子邮件受保护]”作为具有“[电子邮件受保护]","[电子邮件受保护]" 在其字段中。 GROUP BY 显然无法按原样完成此任务。

原文

I'm trying to return duplicate records in a user table where the fields only partially match, and the matching field contents are arbitrary. I'm not sure if I'm explaining it well, so here is the query I might run to get the duplicate members by some unique field:

SELECT MAX(id)
FROM members
WHERE 1
GROUP BY some_unique_field
HAVING COUNT(some_unique_field) > 1

I want to apply this same idea to an email field, but unfortunately our email field can contain multiple e-mails seperated by a comma. For example, I want a member with his email set to "[email protected]" to be returned as a duplicate of another member that has "[email protected]","[email protected]" in their field. GROUP BY obviously will not accomplish this as-is.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

当梦初醒 2024-08-25 08:14:40

这样的方法可能对您有用：

SELECT *
FROM members m1
inner join members m2 on m1.id <> m2.id
    and (
        m1.email = m2.email
        or m1.email like '%,' + m2.email
        or m1.email like m2.email + ',%'
        or m1.email like '%,' + m2.email + ',%'
    )

这取决于您的电子邮件地址在有多个地址时的格式一致性。如果逗号后面始终有空格，或者引号实际上是数据的一部分，您可能需要稍微修改查询。

Something like this might work for you:

SELECT *
FROM members m1
inner join members m2 on m1.id <> m2.id
    and (
        m1.email = m2.email
        or m1.email like '%,' + m2.email
        or m1.email like m2.email + ',%'
        or m1.email like '%,' + m2.email + ',%'
    )

It depends on how consistently your email addresses are formatted when there are more than one. You might need to modify the query slightly if there is always a space after the comma, e.g., or if the quotes are actually part of your data.

回复收藏 0 原文