mysql 统计表中的邮箱用户数量

create table studs(id int,name varchar(30),mail varchar(50));insert into studs values(1,'Jack','[e-mail]');insert into studs values(2,'Mary','[e-mail]');insert into studs values(3,'Alex','[e-mail]');insert into studs values(4,'Jerry','[e-mail]');insert into studs values(5,'Jack','[e-mail]');insert into studs values(6,'Jack','[e-mail]');insert into studs values(7,'Jack','[e-mail]');insert into studs values(8,'Jack','[e-mail]');

drop FUNCTION IF EXISTS subemail;CREATE FUNCTION subemail(in_email VARCHAR(255)) RETURNS VARCHAR(60)BEGINDECLARE i INT DEFAULT 1;set i = locate('@',in_email);set in_email = SUBSTR(in_email,i);return in_email;end;SELECT t.email,count(1) from (SELECT subemail(mail) as email from studs) t GROUP BY t.email;