mysql截取字符串分组输出

SELECT SUBSTRING_INDEX(email, '@', -1) AS domain, COUNT(*) AS user_count
FROM users
GROUP BY domain;

-- 错误示例
SELECT SUBSTRING(email, 1, 5) AS domain FROM users; -- 可能会截取到不完整的域名

-- 正确示例
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain FROM users; -- 正确截取域名

-- 错误示例
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain, COUNT(*) AS user_count
FROM users
GROUP BY domain
HAVING user_count > 0; -- 如果没有符合条件的记录，结果为空

-- 正确示例
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain, COUNT(*) AS user_count
FROM users
GROUP BY domain;

-- 添加索引
ALTER TABLE users ADD INDEX idx_email (email);

-- 优化查询
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain, COUNT(*) AS user_count
FROM users
GROUP BY domain;