MySQL 查询以从电子邮件地址字段计算唯一域 [英] MySQL Query to Count Unique Domains from Email Address field

问题描述

我想更好地了解我的客户正在使用哪些域.我可以在 PHP 中通过 explode 处理每个地址并以这种方式计算域来轻松完成此操作.但我想知道是否有办法通过简单的 MySQL 查询来获取这些信息?

I'd like to get a better idea of what domains my customers are using. I could easily do this in PHP by explodeing each address and counting the domain that way. But I'm wondering if there's a way to get this information with just a plain MySQL query?

这是示例输出的样子:

gmail.com |3942

yahoo.com |3852

hotmail.com |209

... 以此类推，其中第一列是电子邮件地址域，第二列是该域的地址数.

... and so on, where the first column is the email addresses domain, and the 2nd column is the number of addresses at that domain.

推荐答案

你必须这样做:

SELECT substring_index(email, '@', -1) domain, COUNT(*) email_count
FROM table
GROUP BY substring_index(email, '@', -1)

-- If you want to sort as well:
ORDER BY email_count DESC, domain;

这篇关于MySQL 查询以从电子邮件地址字段计算唯一域的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！