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

问题描述

我想了解客户使用的网域。我可以很容易地通过 explode 在PHP中做每个地址并计数域的方式。但是我想知道是否有一个方法来获得这个信息只是一个简单的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?

这是示例输出看起来像：

This is what sample output would look like:

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屋！