MySQL 查询从电子邮件地址字段计算唯一域

Question

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

示例输出如下所示：

gmail.com |第3942

章3852

hotmail.com | 209

...等等，其中第一列是电子邮件地址域，第二列是该域的地址数量。

Answer 1

你必须这样做：

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;

Answer 2

在 WoLpH 的答案中添加 ORDER BY 可以使输出更加清晰：

SELECT substring_index(email, '@', -1), COUNT(*) AS MyCount
FROM `database`.`table`
GROUP BY substring_index(email, '@', -1)
ORDER BY MyCount DESC;

Answer 3

select distinct SUBSTRING(Email, CHARINDEX('@', Email) + 1,LEN(Email) - CHARINDEX ('@', Email)), Count(*) from Tbl_name
Group by SUBSTRING(Email, CHARINDEX('@', Email) + 1,LEN(Email) - CHARINDEX ('@', Email))
order by Count(*) desc

Answer 4

对 Wolph 的原始内容进行了一些小调整，以缩短一点并添加漂亮的列名称和限制结果（以防列表很长）。根据自己的喜好调整限制

select substring_index(email, '@', -1) AS domain, count(*) from TABLE group by domain order by count(*) DESC limit 40;

Answer 5

您可以使用此查询从表中获取域的唯一计数。

SELECT substr(email,INSTR(email,"@")+1),count(substr(email,INSTR(email,"@"))) from YOUR_TABLE group by substr(email,INSTR(email,"@"));

Answer 6

SELECT 
    substring_index(email_address, '@', -1) AS Domain 
   ,COUNT(*) AS MyCount
FROM 
    database_name.table_name
GROUP BY 
    substring_index(email_address, '@', -1)
ORDER BY
    MyCount DESC

Answer 7

怎么样

SELECT COUNT(DISTINCT [what you want])
FROM MyTable

COUNT(DISTINCT expr ,[表达式...])