MySQL邮件使用情况统计方法

 

 　　邮件使用情况统计方法如下：

SELECT SUBSTRING_INDEX([column_name], '@' , -1) AS domain, COUNT(DISTINCT [column_name]) AS mail_count, COUNT([column_name]) AS mail_sum,
COUNT(DISTINCT [column_name])/(SELECT COUNT(DISTINCT [column_name]) FROM [table_name] WHERE [column_name] LIKE '%@%')*100 AS percent
FROM [table_name]
WHERE [column_name] LIKE '%@%'
GROUP BY domain
ORDER BY mail_count DESC LIMIT 30

　　DISTINCT用来排除重复的邮箱地址，domain是邮箱域名，mail_count是统计某域名下不重复的邮箱地址数量，mail_sum是统计某域名下所有邮箱地址数量，percent是统计某域名下不重复邮箱地址数量占总的不重复邮箱地址数量的百分比。