MySQL 查詢用戶註冊郵箱域名分布統計

SQL 語句

SELECT 
    SUBSTRING_INDEX(email, '@', -1) AS mailbox,
    COUNT(*) AS cnt
FROM user_register
WHERE email IS NOT NULL AND email <> ''
GROUP BY mailbox
ORDER BY cnt DESC;

關鍵函數

• SUBSTRING_INDEX(str, delim, count)：count > 0 從左數，count < 0 從右數。-1 取最後一段 (域名)

輸出範例

+-----------+-----+
| mailbox   | cnt |
+-----------+-----+
| gmail.com | 523 |
| qq.com    | 412 |
| 163.com   | 298 |
| ...       | ... |
+-----------+-----+