MySQL 查询以从电子邮件地址字段计算唯一域
我想更好地了解我的客户正在使用哪些域.我可以在 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;
相关文章